MacLochlainns Weblog

I really like Alan Beaulieu’s Learning SQL because its simple, direct, and clearer than other books on SQL. While his focus is MySQL, he does a fair job of injecting a bit about Oracle’s syntax. Comparative concatenation syntax is one of topics I wished he’d spent more time on. Here’s some clarification on cross platform SQL concatenation.

Oracle

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:

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

As to an Oracle specific SQL book recommendation, I’d go with Alan’s as a beginner even though it’s focus is MySQL. By the way, if you don’t own Learning SQL hold off on buying it until the second edition is available in May 2009. If you’re using Oracle and have some basic SQL competence, I’d suggest Mastering Oracle SQL, 2nd Edition by Sanjay Mishra and Alan Beaulieu as a reference. Just make sure you get the 2^nd Edition of it too.

MySQL

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                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

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

SET sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT';

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
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT"

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)

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:

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

As to a MySQL specific SQL book recommendation, I’d go with Alan Beaulieu’s Learning SQL as a beginner. As noted earlier, don’t buy it until the 2^nd Edition ships in May 2009.

Microsoft® Access or 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';

As to a Microsoft® T-SQL book recommendation, I’d go with Itzik Ben-Gan’s Microsoft SQL Server 2008 T-SQL Fundamentals. Just understand, that like most things Microsoft, T-SQL is a dialect and approach that differs substantially from other commercial products.