Microsoft SQL Server Concat


SQL Server 2012

Returns a string that is the result of two or more strings joined together. CONCAT accepts two or more arguments.

SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string'

Note: Unlike concatenating strings using the string concatenation operator (+), when passing a null value to the concat function it will implicitly convert it to an empty string:

SELECT CONCAT('This', NULL, ' is', ' my', ' string'), -- returns 'This is my string'
       'This' + NULL + ' is' + ' my' + ' string' -- returns NULL.

Also arguments of a non-string type will be implicitly converted to a string:

SELECT CONCAT('This', ' is my ', 3, 'rd string') -- returns 'This is my 3rd string'

Non-string type variables will also be converted to string format, no need to manually covert or cast it to string:

SELECT CONCAT('Ram is ', @Age,' years old');  -- returns 'Ram is 23 years old'
SQL Server 2012

Older versions do not support CONCAT function and must use the string concatenation operator (+) instead. Non-string types must be cast or converted to string types in order to concatenate them this way.

SELECT 'This is the number ' + CAST(42 AS VARCHAR(5)) --returns 'This is the number 42'