How do I use the CONCAT function in SQL Server 2008 R2

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How do I use the CONCAT function in SQL Server 2008 R2?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql