MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL Concatenation blues

with 3 comments

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 2nd 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 2nd 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.

Written by maclochlainn

March 9th, 2009 at 12:44 pm

Posted in MySQL,Oracle,sql

Tagged with

3 Responses to 'SQL Concatenation blues'

Subscribe to comments with RSS or TrackBack to 'SQL Concatenation blues'.

  1. Good! …

    But It’s difficult to use “CONCAT” on oracle…

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

    But My SQL:

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

    By the way, thank you for your suggestion about books.

    Surachart Opun

    10 Mar 09 at 1:02 pm

  2. i have a question too. i’m working in access (2007)and I have a table ADRESS with adres (= key) zipcode and a table CONTACTS wits adres first name last name (those 3 together as key) mobile email birthday et cetera.

    Now i want to make a query that qives me one adres, but the names of all the persons who live on that specific adress. what do I need to do, because the famous concat is indeed not usable in Access :(

    Thanks a lot!!!

    sanne

    10 Sep 09 at 6:00 am

  3. SQL Server 2012 supports CONCAT with an unlimited number of parameters the same way as MySQL.

    Is also a good example of Microsoft trying to extend there server. I which ORACLE would do the same. There’s nothing quite like having to code queries in more then one format for something as silly as not supporting “+” to append strings. Maybe next SQL Server they will support “||” for string concat (“|” is already bitwise OR in SQL Server, I expect they could add support).

    DarrenMB

    10 Dec 13 at 11:25 am

Leave a Reply