|| 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).

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

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

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

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.

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Online SQL Conversion Tool

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.