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?
This question is related to
sql-server
sql-server-2008
tsql
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)
.
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.
(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))
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 + .....
Yes the function is not in sql 2008. You can use the cast operation to do that.
For example we have employee
table and you want name
with applydate
.
so you can use
Select cast(name as varchar) + cast(applydate as varchar) from employee
It will work where concat function is not working.
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.
Source: Stackoverflow.com