Concat

Often when dealing with text in an SQL statements it's desirable to join two or more strings together. For example we might want to join a persons title and surname to produce a suitable heading for an address block.

This joining of words is called concatenation and in Oracle is achieved using the pipe character twice like so ||

 
SQL> select title||' '||surname from person;

TITLE||''||SURNAME
-----------------------------------------
Mr Miller

In MySQL concatenation is achieved using a function called concat. All we need to do is call the concat function from the SQL statement passing in the columns or text we wish to concatenate.

mysql> select concat(title,' ',surname) from person
+---------------------------+
| concat(title,' ',surname) |
+---------------------------+
| Mr Miller                 |
+---------------------------+
1 row in set (0.02 sec)

However we can use an SQL Server Mode parameter to allow us to use the pipe character as we would in Oracle. SQL Server Modes are settings which change the behaviour of the server for an individual client. To use pipes for concatenation we can use the PIPES_AS_CONCAT option. This is set as follows.

 
set sql_mode = 'PIPES_AS_CONCAT';

So we can see this in action like so.

mysql> select title||' '||surname from person;
+---------------------+
| title||' '||surname |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.02 sec)

mysql> set sql_mode = 'PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> select title||' '||surname from person;
+---------------------+
| title||' '||surname |
+---------------------+
| Mr Miller           |
+---------------------+
1 row in set (0.00 sec)

You may have noticed that the first call using the pipe characters does not cause an error, this is because the double pipe is used as a synonum for OR in MySQL. You should use PIPES_AS_CONCAT with caution for this reason.