|| String Concatenation Operator - Oracle to MySQL Migration

In Oracle you can use || operator to concatenate strings. In MySQL you have to use CONCAT function.

Oracle:

   -- Concatenate strings
   SELECT 'New ' || 'York ' || 'City' FROM dual;
   # New York City

MySQL:

   -- Concatenate strings
   SELECT CONCAT('New ', 'York ', 'City');
   # New York City

Note that Oracle || operator and MySQL CONCAT function handle NULL values differently (see below).

|| to CONCAT Conversion Overview

Oracle || operator to MySQL CONCAT conversion:

Oracle MySQL
Syntax string1 || string2 || … CONCAT(string1, string2, …)
NULL Value Treated as '' (empty string) Result is NULL
All NULLs Result is NULL Result is NULL
Implicit Conversion Number and datetime Number and datetime
Alternatives CONCAT function 2 parameters only || operator if PIPES_AS_CONCAT is set

Last Update: Oracle 11g Release 2 and MySQL 5.6

|| to CONCAT Conversion Details

In Oracle, a NULL value in any string expression is treated as '' (empty string).

Oracle:

   SELECT 'The city' || ' is ' || NULL FROM dual;
   # The city is

But if all expressions evaluate to NULL, || operator returns NULL, not empty string.

   SELECT NULL || NULL || NULL FROM dual;
   # NULL

In MySQL, CONCAT function returns NULL if any expression is NULL:

MySQL:

   SELECT CONCAT('The city', ' is ', NULL);
   # NULL

In MySQL, you have to use IFNULL function to replace nullable expressions with empty string to get the same result as in Oracle:

   SELECT CONCAT('The city', ' is ', IFNULL(NULL, ''));
   # The city is

Non-String Parameters Implicit Conversion

Both Oracle || operator and MySQL CONCAT function implicitly convert numbers and datetime values to string before concatenation

Oracle:

  SELECT 1 || 'st' FROM dual;
  # 1st
 
  SELECT 'Today is ' || SYSDATE FROM dual;
  # Today is 28-MAY-12

MySQL:

  SELECT CONCAT(1, 'st');
  # 1st
 
  SELECT CONCAT('Today is ', SYSDATE());
  # Today is 2013-02-28 12:05:43

Using || Operator in MySQL

You can enable || operator in MySQL if you set PIPES_AS_CONCAT option.

MySQL:

  -- Save sql_mode settings
  SET @old_sql_mode=@@sql_mode;
 
  -- Enable || for string concatenation
  SET @@sql_mode=PIPES_AS_CONCAT;
 
  SELECT 'The city' || ' is ' || 'Paris';
  # The city is Paris
 
  -- If any expression is NULL, the entire result is NULL
  SELECT 'The city' || ' is ' || NULL;
  # NULL
 
  SET @@sql_mode=@old_sql_mode;

Note than even when || operator is enabled, the concatenation result is NULL if any expression in NULL.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - February 2013.

You could leave a comment if you were logged in.