CONCAT

CONCAT (Transact-SQL)

Brief Description:      Returns a string that is the result of concatenating two or more string values

Syntax:                       CONCAT ( string_value1, string_value2 [, string_valueN ] )

Availability:               All Versions of SQL Server 2012

Details:                       CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions

Examples:

1.  select concat( AddressLine1, ' ',AddressLine2,' ', city )  from [Person].[Address]

2.  select 1234.15 + ' abc'

3.  select concat( ' Today is ' , getdate()

Alternatives:                        String  concatenation can be carried out with  ‘+’ Operator

Comparison  with ‘+’ :

1.  select concat( 'Mahatma', null, ' Gandhi') as 'Father of the Nation'

Output

Father of the Nation

Mahatma Gandhi

select  'Mahatma' + null + ' Gandhi' as 'Father of the Nation'

Output

Father of the Nation

NULL

2.  select concat( 1234.56,' abc') as concatnumetic

Output

concatnumetic

1234.56 abc

select 1234.56 + ' abc'  as concatnumetic

Error

Msg 8114, Level 16, State 5, Line 2

Error converting data type varchar to numeric

3.  select concat( ' Today is - ' , getdate() ) as CurrentDateTime

Output

CurrentDateTime

Today is - Mar 25 2012  9:38PM

select ' Today is ' +   getdate() as CurrentDateTime

Error

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string

4.  declare @strone varchar(8000),@strtwo varchar(8000),@strthree varchar(max)

       set @strone   = replicate ('m',8000)

       set @strtwo   = replicate ('a',8000)

       set @strthree = replicate ('p',8000)

       select datalength(concat( @strone,@strtwo) )   as DatalengthOneTwo

      

Output

DatalengthOneTwo

8000

       select datalength(concat( @strone,@strthree) ) as DatalengthOneThree

Output

DatalengthOneThree

16000

select datalength(@strone + @strtwo)    as DatalengthOneTwo

Output

DatalengthOneTwo

8000

       select datalength(@strone + @strthree)  as DatalengthOneThree

Output

DatalengthOneThree

16000

Pros:  

·         If NULL is involved in string concatenation automatically converted to empty string. ISNULL/COALESCE  function not required

·         Concatenation of numeric, datetime  is implicit

·         Better than concatenation Operator  ‘+’

Cons:  

·         In some cases converting NULL to  empty string  not desirable

·         If  arguments are varchar, maximum concatenation up to 8000 characters only , remaining  value is truncated