|| String Concatenation Operator - Oracle to SQL Server Migration

|| operator concatenates one or more strings into a single string in Oracle.

Quick Example:

   -- Concatenate strings 'New ' and 'York'
   SELECT 'New ' || 'York' FROM dual;
   -- Result: New York

|| Operator Overview

Summary information:

Syntax string_expression1 || string_expression2 || …
NULL Values NULL value in any expression is treated as '' (empty string)
But || returns NULL if all expressions are NULL
Expression Conversion Number and datetime expressions are implicitly converted to string before concatenation
Alternatives CONCAT function 2 parameters only NULL is '' (empty string)

Last Update: Oracle 11g Release 2

|| Operator Details

|| operator concatenates one or more string expressions into a single string.

Oracle:

   SELECT 'The city' || ' is ' || 'Paris' FROM dual;
   -- Result: The city is Paris

NULL Is Empty String

NULL value in any string expression is treated as '' (empty string).

Oracle:

   SELECT 'The city' || ' is ' || NULL FROM dual;
   -- Result: The city is

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

Oracle:

   SELECT NVL(NULL || NULL || NULL, 'Null value') FROM dual;
   -- Result: Null value

Non-String Parameters Implicit Conversion

|| operator implicitly converts numbers and datetime values to string before concatenation.

Oracle:

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

Converting || Operator to SQL Server

You can convert Oracle || string concatenation operator to + operator or CONCAT function in SQL Server and SQL Azure:

+ Operator
If any value is NULL, the result is NULL, unless CONCAT_NULL_YIELDS_NULL is OFF
Explicit casting required
CONCAT Function
NULL is '' (empty string), does not depend on CONCAT_NULL_YIELDS_NULL
More than 2 expressions can be specified
Available since SQL Server 2012

Last Update: Microsoft SQL Server 2012

In SQL Server, both + operator and CONCAT function allow you to concatenate more than 2 strings.

SQL Server:

   SELECT 'The city' + ' is ' + 'Paris';
   -- Result: The city is Paris
 
   SELECT CONCAT('The city', ' is ', 'Paris');
   -- Result: The city is Paris

NULL Values

In SQL Server, if any expression is NULL, + operator returns NULL for the entire expression (by default), while CONCAT treats NULL value in any expression as '' (empty string).

SQL Server:

   SELECT 'The city' + ' is ' + NULL;
   -- Result: NULL
 
   SELECT CONCAT('The city', ' is ', NULL);
   -- Result: The city is

But by setting CONCAT_NULL_YIELDS_NULL to OFF, you can specify to treat NULL as '' (empty string) by + operator as well.

SQL Server:

   SET CONCAT_NULL_YIELDS_NULL OFF
 
   SELECT 'The city' + ' is ' + NULL;
   -- Result: The city is

Note that CONCAT_NULL_YIELDS_NULL does not apply to CONCAT function, it always treats NULL as '' (empty string).

Unfortunately (from migration perspective), Microsoft does not recommend using SET CONCAT_NULL_YIELDS_NULL OFF, and plans to always set this option to ON, so you should use CONCAT function or ISNULL function for individual expressions to replace NULL with '' (empty string) to get the same behavior as in Oracle.

SQL Server:

   SELECT 'The city' + ' is ' + ISNULL(NULL, '');
   -- Result: The city is

Non-String Parameters Implicit Conversion

Unlike || in Oracle, + operator in SQL Server requires explicit conversion of numbers and datetime values to string before concatenation.

SQL Server:

  SELECT 1 + 'st';
  -- Error:
  -- Msg 245, Level 16, State 1, Line 1
  -- Conversion failed when converting the varchar value 'st' to data type int.
 
  SELECT 'Today is ' + GETDATE();
  -- Error:
  -- Msg 241, Level 16, State 1, Line 1
  -- Conversion failed when converting date and/or time from character string.

At the same time, in SQL Server, CONCAT function implicitly converts numbers and datetime values to string before concatenation (the same as Oracle || does).

SQL Server:

  SELECT CONCAT(1, 'st');
  -- Result: 1st
 
  SELECT CONCAT('Today is ', GETDATE());
  -- Result: Today is May 28 2012  6:13PM

Resources

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 - May 2012.

You could leave a comment if you were logged in.