Example
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:
DECLARE @Age INT=23;
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'
