How to use the CONCAT function in SQL Server 2008 R2?

Question

I was looking for the CONCAT function in SQL Server 2008 R2. I found a 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.

Is there a CONCAT function in SQL Server 2008 R2?

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

Answer 1

CONCAT is new to SQL Server 2012. The link you provided makes this clear; it is not a function of previous versions, including 2008 R2.

That this 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 gratefully states that SQL Server provides an implementation of the ODBC CONCAT function .

Answer 2

For completeness only - in SQL 2008, you must use the plus + operator to perform string concatenation.

See the MSDN link for sample code. Starting with SQL 2012, you can use the new CONCAT function.

Answer 3

I suggest you drop all the columns before concatenating them

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

This should work for you.

Answer 4

CONCAT, as indicated, is not supported until SQL Server 2012. However, you can concatenate simply by using the + operator, as suggested. But be careful, this statement will cause an error if the first operand is a number, because it believes that it will add and not concatenate. To solve this problem, just add "in front". for example

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

will result in an error, but '' + someNumber + 'someString' + ...... will work fine.

Also, if there are two numbers you need to combine, make sure you add `` in between, e.g.

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

Answer 5

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

Only listing or conversion if any type of field is different from others.

Paste the value should be in the right place, you need to paste it. Using "how" will give you an error message.

i.e.

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

Answer 6

NULL safe fall in replacement approximations for SQLCAD 2012 2012 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 combine several excellent answers and caveats raised by other posters, including @Martin Smith, @Svish and @ vasin1987.

These parameters add NULL to the '' (empty line) casting to safely handle NULL when taking into account the different behavior of the + operator related to certain operands.

Note that the ODBC Scaler Function is limited to two arguments, while the + approach is scalable for many arguments as needed.

Also note the potential problem identified by @Swifty regarding the default size of varchar , here fixed by varchar(MAX) .