Differences

This shows you the differences between two versions of the page.

oracle-to-sql-server:string_concat [February 17, 2013 6:58 am] (current)
sqlines created
Line 1: Line 1:
 +====== || String Concatenation Operator - Oracle to SQL Server Migration ======
 +%%||%% operator concatenates one or more strings into a single string in Oracle.
 +
 +**//Quick Example//**:
 +
 +<code sql>
 +   -- Concatenate strings 'New ' and 'York'
 +   SELECT 'New ' || 'York' FROM dual;
 +   -- Result: New York
 +</code>
 +
 +===== || 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 {{:exclamation.png|}} | NULL is  %%''%% (empty string) |
 +
 +**Last Update:** Oracle 11g Release 2
 +
 +===== || Operator Details =====
 +
 +%%||%% operator concatenates one or more string expressions into a single string.
 +
 +**Oracle**:
 +
 +<code sql>
 +   SELECT 'The city' || ' is ' || 'Paris' FROM dual;
 +   -- Result: The city is Paris
 +</code>
 +
 +==== NULL Is Empty String ====
 +
 +NULL value in any string expression is treated as %%''%% (empty string).
 +
 +**Oracle**:
 +
 +<code sql>
 +   SELECT 'The city' || ' is ' || NULL FROM dual;
 +   -- Result: The city is
 +</code>
 +
 +But if **all** expressions evaluate to NULL, %%||%% operator returns NULL, not empty string.
 +
 +**Oracle**:
 +
 +<code sql>
 +   SELECT NVL(NULL || NULL || NULL, 'Null value') FROM dual;
 +   -- Result: Null value
 +</code>
 +
 +==== Non-String Parameters Implicit Conversion ====
 +
 +%%||%% operator //implicitly// converts numbers and datetime values to string before concatenation.
 +
 +**Oracle**:
 +
 +<code sql>
 +  SELECT 1 || 'st' FROM dual;
 +  -- Result: 1st
 +
 +  SELECT 'Today is ' || SYSDATE FROM dual;
 +  -- Result: Today is 28-MAY-12
 +</code>
 +
 +===== Converting || Operator to SQL Server =====
 +
 +You can convert Oracle %%||%% string concatenation operator to [[/sql-server/string_concat|+ operator]] or CONCAT function in SQL Server and SQL Azure:
 +
 +| **[[/sql-server/string_concat|+ Operator]]** |
 +| If any value is NULL, the result is NULL, unless CONCAT_NULL_YIELDS_NULL is OFF {{:exclamation.png|}}|
 +| Explicit casting required {{:exclamation.png|}} |
 +| **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 {{:exclamation.png|}} |
 +
 +**Last Update:** Microsoft SQL Server 2012
 +
 +In SQL Server, both + operator and CONCAT function allow you to concatenate more than 2 strings.
 +
 +**SQL Server**:
 +
 +<code sql>
 +   SELECT 'The city' + ' is ' + 'Paris';
 +   -- Result: The city is Paris
 +
 +   SELECT CONCAT('The city', ' is ', 'Paris');
 +   -- Result: The city is Paris
 +</code>
 +
 +==== 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**:
 +
 +<code sql>
 +   SELECT 'The city' + ' is ' + NULL;
 +   -- Result: NULL
 +
 +   SELECT CONCAT('The city', ' is ', NULL);
 +   -- Result: The city is
 +</code>
 +
 +But by setting CONCAT_NULL_YIELDS_NULL to OFF, you can specify to treat NULL as %%''%% (empty string) by + operator as well.
 +
 +**SQL Server**:
 +
 +<code sql>
 +   SET CONCAT_NULL_YIELDS_NULL OFF
 +  
 +   SELECT 'The city' + ' is ' + NULL;
 +   -- Result: The city is
 +</code>
 +
 +Note that CONCAT_NULL_YIELDS_NULL does not apply to CONCAT function, it always treats NULL as %%''%% (empty string).
 +
 +Unfortunately (from migration perspective), Microsoft [[http://msdn.microsoft.com/en-us/library/ms176056.aspx|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**:
 +
 +<code sql>
 +   SELECT 'The city' + ' is ' + ISNULL(NULL, '');
 +   -- Result: The city is
 +</code>
 +
 +==== 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**:
 +
 +<code sql>
 +  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.
 +</code>
 +
 +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**:
 +
 +<code sql>
 +  SELECT CONCAT(1, 'st');
 +  -- Result: 1st
 +
 +  SELECT CONCAT('Today is ', GETDATE());
 +  -- Result: Today is May 28 2012  6:13PM
 +</code>
 +
 +===== Resources =====
 +
 +  * [[/oracle|Oracle to SQL Server Migration]]
 +  * [[/sql-server/string_concat|+ Operator in SQL Server]]
 +
 +**Oracle 11g R2 SQL Language Reference**
 +
 +  * [[http://docs.oracle.com/cd/E11882_01/server.112/e26088/operators003.htm||| Concatenation Operator]]
 +  * [[http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions033.htm|CONCAT Function]]
 +
 +**Microsoft SQL Server 2012 - Books Online**
 +
 +  * [[http://msdn.microsoft.com/en-us/library/ms177561.aspx|+ String Concatenation]]
 +  * [[http://msdn.microsoft.com/en-us/library/hh231515|CONCAT Function]]
 +  * [[http://msdn.microsoft.com/en-us/library/ms176056.aspx|SET CONCAT_NULL_YIELDS_NULL]]
 +
 +===== Database and SQL Migration Tools =====
 +
 +  * [[/tool|SQLines - Database and SQL Conversion Tool]]
 +  * [[/sqldata|SQLines Data - Data Transfer and Database Schema Migration Tool]]
 +  * [[http://www.sqlines.com/online|Free Online SQL Conversion Tool]]
 +
 +  * [[/oracle-to-sql-server|Oracle to SQL Server Migration Reference]]
 +
 +===== 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.
 +
 +~~NOTOC~~
 +~~DISCUSSION~~