|| 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:

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