SQL Concatenation blues
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.