|| 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
Summary information:
Last Update: Oracle 11g Release 2
|| 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 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
|| 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
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
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
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
Oracle 11g R2 SQL Language Reference
Microsoft SQL Server 2012 - Books Online
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.