SQL Concatenate Functions in MySQL with Examples

In this tutorial, you will learn various ways to concatenate two or more string together by using concat function which is provided by MySQL.

Almost RMDMSs support us to concatenate two or more strings together by using different implementations. MS SQL server us operator plus (+) to concatenate strings. Oracle only allows you to concatenate two strings with concat function or operator . MySQL supports a more flexible way by enabling us to concatenate more than two strings or even concatenate strings with predefined separator. Let's start with each concatenate function.

Concat Function

CONCAT(str1,str2,...)The concat function is used to concatenate two or more string and returns concatenating string. If the arguments are numeric, they will be converted to strings before concatenating. If any of argument in the argument list is NULL, the concat function will return NULL.

Here are several examples to demonstrate the concat function. We will use the sample database to practice.

In order to display the first 5 full name of contacts of the customers we use concat function to concatenate the first name and last name and a separator between them. Here is the query:

SELECT CONCAT(contactLastname,', ',contactFirstname) fullname
FROM customers
LIMIT 5

The output result is

fullname
------------------
Schmitt, Carine
King, Jean
Ferguson, Peter
Labrune, Janine
Bergulfsen, Jonas

Concat with Separator Function
MySQL also supports concat_ws function which allows us to concatenate two or more than two strings with a predefined separator. The syntax of the concat_ws function is:

CONCAT_WS(seperator,str1,str2,...) The first parameter is the predefined separator you specified and the others are the string you want to concatenate. the result is the concatenating string with separator between each. For example, you can achieve the same result in the above example by using concat_ws function instead of concat function.

SELECT CONCAT_WS(', ',contactLastname,contactFirstname) fullname
FROM customers
LIMIT 5
Here is another example of using concat_ws to get address format of customers.

SELECT CONCAT_WS(char(13),
CONCAT_WS(' ',contactLastname,contactFirstname),addressLine1,addressLine2,CONCAT_W
(' ',postalCode,city),country,CONCAT_WS(char(13),'')) AS Customer_Address
FROM customers
LIMIT 2

Here is the output result

Customer_Address
---------------------------------------------------
Schmitt Carine
54, rue Royale
44000 Nantes
France

King Jean
8489 Strong St.
83030 Las Vegas
USA

Compare Two Tables to Find Unmatched Records ( Membandingkan dua table untuk menemukan record yang tidak sama )

In database programming, sometimes you have to compare two tables (or two views of more than two tables) to find the unmatched records and the difference between two records in two tables with the same identity. As an example, in database migration you have a legacy database and new database with two different database schemata. Your task is to migration all data from the legacy database to the new one so how do you validate your result? At this time, you have to use this technique to see your result; what records you missed in the legacy database; what records you migrated with the wrong result.
One of the fastest and easiest ways to do so is using UNION. The idea is we use UNION to union two tables on all columns which we want to compare. Then we group the union on all columns which we want to compare. If the all columns are identical we get COUNT (*) equal 2 otherwise all unmatched records and the records in one table but not in other table will have COUNT (*) equal 1.

SELECT MIN (tbl_name) AS tbl_name, PK, column_list
FROM
(SELECT ' source_table ' as tbl_name, S.PK, S.column_list FROM source_table AS S UNION ALL SELECT 'destination_table' as tbl_name, D.PK, D.column_list FROM destination_table AS D ) AS alias_table
GROUP BY PK, column_list HAVING COUNT (*) = 1
ORDER BY PK

In the above query:

# tbl_name is just additional column name to display the name of tables you want to compare. These are source_table and destination_table

# PK is identity name of source_table and destination_table

# column_list is the list of column you want to compare

# The MIN aggregate on table_name is just arbitrary. It is used because we only returns group of rows in which there was no consolidation with GROUP BY.

If two tables are identical on the column list you compared, no row will return.

Select the nth Highest Record in a Database Table

In this tutorial, you will learn how to select the nth highest record in a database table in various techniques. These techniques are very useful, for instance you may want to see the product which has second most expensive in Products database table.
The first idea is we get the nth highest record and sort them in ascending order. The nth highest record is the last record in the result set. And then we order the result set in descending order and get the first one. Here is SQL script to accomplish it:
Get the Nth highest result set in ascending order:

SELECT * FROM table_name ORDER BY column_name AS C LIMIT n

Get the nth highest record:

SELECT * FROM ( SELECT * FROM table_name ORDER BY column_name AS C LIMIT n) AS tbl ORDER BY column_name DESC LIMIT 1

Luckily, with LIMIT clause you can rewrite the query as follows:

SELECT *FROM table_name ORDER BY column_name DESC LIMIT n - 1, 1

The query just returns the first row after n-1 row(s) so you get the nth highest record.
For example, if you want to get the second most expensive product (n = 2) in Products database table. You just perform the following query:

SELECT productCode, productName, buyPrice
FROM products
ORDER BY buyPrice desc
LIMIT 1, 1

Here is the result

+-------------+--------------------------------+----------+
productCode productName buyPrice
+-------------+--------------------------------+----------+
S18_2238 1998 Chrysler Plymouth Prowler 101.51
+-------------+--------------------------------+----------+
1 row in set (0.00 sec)

The second technique to get the nth highest record is using subquery

SELECT *FROM table_name AS a
WHERE n - 1 = (SELECT COUNT(primary_key_column) FROM products b WHERE b.column_name > a. column_name)

And we achieve the same result as the first technique on Products table to get the second most expensive product by performing the following query:

SELECT productCode, productName, buyPrice
FROM products a
WHERE 1 = ( SELECT COUNT(productCode) FROM products b WHERE b.buyPrice > a.buyPrice)

Count a Number of Records in a Database Table

In order to count a number of records in a database table we can use standard SQL COUNT function. The syntax is quite simple as follows:

SELECT COUNT(*) AS total_founds
FROM table_name
The query will return the total record of the table. We can use WHERE clause in SELECT statement to filter records in the table we want to count as follows:

SELECT COUNT(*) AS total_founds
FROM table_name
WHERE conditions
The query will only returns the number of records or rows which match the conditions in WHERE clause.

Be noted that the COUNT(*) will return all rows in the database table even though a specified column value in a database table is NULL. In order to count records without NULL value in a specified column, we can use COUNT(column) instead. Here is the syntax:

SELECT COUNT(column)
FROM table_name
Let's practice with a couple of examples to understand more how SQL COUNT works in MySQL:

We can count all the offices in the office table by executing following query:

SELECT COUNT(*) AS total_office FROM offices
+--------------+
| total_office |
+--------------+
| 7 |
+--------------+
To count all office which has state we can operate following query:

SELECT COUNT(state) AS total_office_with_state
FROM offices
+-------------------------+
| total_office_with_state |
+-------------------------+
| 4 |
+-------------------------+

Using Regular Expression in MySQL

Regular expression is a powerful tool which gives you a concise and flexible way to identify strings of text, for instance characters, words or patterns of characters. As an example, you can use regular expression to find email, IP address, phone number, social security number… which has their own patterns. In computing world, regular expression usually refers as regex in short. Regular expression uses its own language with special syntax which can be interpreted by a regular expression processor. Regular expression is now widely use in almost platform from programming language to database world. MySQL built-in supports regular expression which allows you to search for strings of text by using special operator call REGEXP. Here is the syntax:

SELECT column_list
FROM table_name
WHERE column REGEXP pattern

The advantage of using regular expression is you are not limited to search for a string based on a fixed pattern in comparison with LIKE operator. In addition, patterns in regular expression can help you to reduce the lengthy SQL statements with AND and OR operators in condition of WHERE clause.

The disadvantage of using regular expression is that it is quite difficult to understand and maintain such a complicated pattern. Therefore you should write comment the meaning of regular expression when you use it in SQL statement. In some cases, the performance of data retrieval may degrade if you use complicated pattern in non-indexed column.

Let’s take a look at an example of using regular expression in MySQL. Suppose you want to find out all employees who has last name starting with M, B or T. we can use regular expression in SQL statement like this:

SELECT lastname,firstname
FROM employees
WHERE lastname REGEXP '^(M|B|T)'Here is the output:

lastname firstname
-------- ---------
Murphy Diane
Bondur Gerard
Bow Anthony
Thompson Leslie
Tseng Foon Yue
Bondur Loui
Bott Larry
Marsh Peter

The pattern says find all last name which has the initial characters is started with M or (|) B or T.

MySQL Copying Data from one Table to a New One

In this tutorial, you will learn how to copy data from one table into a new table by using SQL CREATE TABLE and SELECT statement. Copying data from an existing table to a new one is useful in some cases such as backing up data, create a copying of real data for testing. In order to copy data from one table to a new one you can use the following command:


CREATE TABLE new_table
SELECT *
FROM existing_table
MySQL will first create a new table with name as indicated after CREATE TABLE statement, new_table in this case. Then it will fill the new table with all the data from an existing table (existing_table).

To copy a part of data from an existing table, you can use WHERE clause to filter the selected data base on conditions. The command is as follows:



CREATE TABLE new_table
SELECT *
FROM existing_table
WHERE conditions


It is very important to check whether table you want to create is existed or not, you should use IF NOT EXIST after CREATE TABLE statement. The full sql command of copying data from an existing table to a new one will be as follows:


CREATE TABLE IF NOT EXISTS new_table
SELECT *
FROM existing_table
WHERE conditions
Here is the example of using copying data command. We have Office data table, now we can copy the table from this table into a new one by using the following command:


CREATE TABLE IF NOT EXISTS offices_bk
SELECT * FROM offices
If we need only copy all offices in US, so we can use WHERE condition for it as follows:


CREATE TABLE IF NOT EXISTS offices_usa
SELECT *
FROM offices
WHERE country = 'USA'

Select Random Records in Database Table

In this tip, you will learn various techniques to select random items from a database table in MySQL.

MySQL does not have any automatic way to select random items from a database table. In some programming tasks, it is required and very useful to select random items from a result set such as:

■You'll need to select a random picture to display it for a category, so each time when the visitor come to your website, they will see different pictures displaying on the categories.
■You'll need to select a random banner to display to visitors.
■You'll need to pick a row in "quote of the day" application.
■And more and more cases you can think of...
In order to achieve this result, MySQL allows to do that via RAND function. To select a random value from a result set you perform the following query:

SELECT * FROM table ORDER BY RAND() LIMIT 1The key technique used above is randomizing the returned records and then pick the first one. If you want to select n random items just change the parameter after LIMIT as follows:

SELECT * FROM table ORDER BY RAND() LIMIT nThe technique as demonstrated above work very well with the small table. With the table which has many records, it could be very slow because we have to sort the entire table to pick random items. To work around this problem, we will use another technique as demonstrated below:

1.First we select a random ID(s) of a column. This column should be the primary key and the value is in sequential range.
2.Then pick the rows based on the ID(s) we selected
The SQL script for doing this is as follows:

SET @ID = FLOOR(RAND( )* N) + 1;
SELECT * FROM table WHERE ID >= @ID LIMIT 1