Database Tutorial

Course Tutorial Site

Site Admin

SQL Concatenation

without comments

SQL statements let you concatenate, or glue together, strings. It works with string literals or with columns that contain strings or formatted dates.

String Concatenation


Oracle supports two forms of string concatenation. Concatenation for those new to the idea means gluing two strings into one, or three strings into one, et cetera. One uses the || operator, which looks like two pipes. You can use the || operator between any number of string elements to glue them together. A quick example of the || operator that returns an ABCD string is:

SELECT 'A' || 'B' || 'C' || 'D' FROM dual;

The Oracle database also supports the CONCAT operator that many use in MySQL. Those converting to an Oracle database should beware the difference between how the CONCAT function is implemented in Oracle versus MySQL. In an Oracle database, the CONCAT function only takes two arguments. When you call it with three or more arguments like this:

SELECT CONCAT('A','B','C','D') FROM dual;

It raises the following exception:

SELECT CONCAT('A','B','C','D') FROM dual
ERROR at line 1:
ORA-00909: invalid NUMBER OF arguments

You can use the CONCAT function to process more than two arguments but you must do so by calling the function recursively. You’d do it like this if you must use it:



MySQL appears to support the two same forms of string concatenation as an Oracle database. The one that uses the || operator (known as pipe concatenation), actually only returns a zero unless you configure the sql_mode to allow pipe concatenation.

The following concatenation statement uses pipe concatenation:

mysql> SELECT 'A'||'B'||'C'||'D';
| 'A'||'B'||'C'||'D' |
|                  0 |
1 ROW IN SET, 4 warnings (0.00 sec)

By default, this fails and returns a zero unless you’ve added the PIPES_AS_CONCAT mode to your sql_mode variable. It returns a zero because it attempts to see whether either of the adjoining elements are true. Strings inherently fail to resolve as expressions or Boolean values and the function returns a zero, which means the composite expression was evaluated as false.

You can query the sql_mode variable as follows. The default values are shown in the results.

mysql> SELECT @@sql_mode;
| @@sql_mode                                                     |
1 ROW IN SET (0.00 sec)

You can modify the sql_mode as follows from the command line:


If you want to make this a permanent change, you can edit the my.ini file in Windows or the my.conf file in Unix or Linux. The following shows the modified line in a configuration file.

# Set the SQL mode to strict

With these changes pipe concatenation works in MySQL, as follows:

mysql> SELECT 'A'||'B'||'C'||'D';
| 'A'||'B'||'C'||'D' |
| ABCD               |
1 ROW IN SET (0.02 sec)

The problem with pipe concatenation in MySQL is that you can’t use it inside function calls because the order of operation fails doesn’t recognize pipe concatenation. It returns various errors, and you really should avoid pipe concatenation or always remember to exclude it from function call semantics.

You can use the CONCAT function to glue any number of string elements together when you’ve no control of the sql_mode variable. The CONCAT function in MySQL takes several arguments. I’ve never needed to use more than the limit and suspect that there isn’t one (based on the documentation). It appears to use a recursive algorithm for parameter processing. Please post a note correcting me if I’m wrong on this.

You call the CONCAT function like this:


SQL Server

Microsoft® SQL Server doesn’t support two forms of string concatenation like Oracle and MySQL. You can only use the + operator. There is no CONCAT function in Microsoft® Access or SQL Server. A quick example of the + operator in Microsoft’s SQL returns an ABCD string like this:

SELECT 'A' + 'B' + 'C' + 'D';

Written by michaelmclaughlin

October 16th, 2009 at 11:11 pm

Posted in

Leave a Reply