How do I use the CONCAT function in SQL Server 200

Question

I was looking for a CONCAT function in SQL Server 2008 R2. I found the link for this function. But when I use this function, it gives the following error:

Msg 195, Level 15, State 10, Line 7
'CONCAT' is not a recognized built-in function name.

Does the CONCAT function exists in SQL Server 2008 R2?

If not, how do I concatenate strings in SQL Server 2008 R2?

Answer 1

I suggest you cast all columns before you concat them

cast('data1' as varchar) + cast('data2' as varchar) + cast('data3' as varchar)

This should work for you.

Answer 2

Just for completeness - in SQL 2008 you would use the plus + operator to perform string concatenation.

Take a look at the MSDN reference with sample code. Starting with SQL 2012, you may wish to use the new CONCAT function.

Answer 3

CONCAT is new to SQL Server 2012. The link you gave makes this clear, it is not a function on Previous Versions, including 2008 R2.

That it is part of SQL Server 2012 can be seen in the document tree:

SQL Server 2012  
Product Documentation  
Books Online for SQL Server 2012  
Database Engine  
  Transact-SQL Reference (Database Engine)  
    Built-in Functions (Transact-SQL)  
      String Functions (Transact-SQL)  

EDIT Martin Smith helpfully points out that SQL Server provides an implementation of ODBC's CONCAT function.

Answer 4

(city + ', ' + state + ' ' + zip) as ctstzip for select
(city + ', ' + state + ' ' + zip) for insert

Only cast or convert if any field type is different from others.

On insert the value needs to be in the correct spot you need it be inserted. Using "as" will give you an error.

i.e.

Insert into testtable (ctstzip) Values ((city + ', ' + state + ' ' + zip))

Answer 5

NULL safe drop in replacement approximations for SQL Server 2012 CONCAT function

SQL Server 2012:

SELECT CONCAT(data1, data2)

PRE SQL 2012 (Two Solutions):

SELECT {fn CONCAT(ISNULL(data1, ''), ISNULL(data2, ''))}

SELECT ISNULL(CAST(data1 AS varchar(MAX)), '') + ISNULL(CAST(data2 AS varchar(MAX)), '')

These two solutions collate several excellent answers and caveats raised by other posters including @Martin Smith, @Svish and @vasin1987.

These options add NULL to '' (empty string) casting for safe NULL handling while accounting for the varying behaviour of the + operator pertaining to specific operands.

Note the ODBC Scaler Function solution is limited to 2 arguments whereas the + operator approach is scalable to many arguments as needed.

Note also the potential issue identified by @Swifty regarding the default varchar size here remedied by varchar(MAX).

Answer 6

CONCAT, as stated, is not supported prior to SQL Server 2012. However you can concatenate simply using the + operator as suggested. But beware, this operator will throw an error if the first operand is a number since it thinks will be adding and not concatenating. To resolve this issue just add '' in front. For example

someNumber + 'someString' + .... + lastVariableToConcatenate

will raise an error BUT '' + someNumber + 'someString' + ...... will work just fine.

Also, if there are two numbers to be concatenated make sure you add a '' between them, like so

.... + someNumber + '' + someOtherNumber + .....