115

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?

George Stocker
  • 57,289
  • 29
  • 176
  • 237
Mitesh Budhabhatti
  • 1,583
  • 2
  • 16
  • 20
  • @Oded I just give try to execute the stmt - select concat('b', 'a') – Mitesh Budhabhatti May 11 '12 at 14:18
  • 1
    @marc_s: The documentation does have some indication that it's for SQL Server 2012, but there's no indication that `CONCAT` is *new* for 2012. – Gabe Jul 24 '12 at 02:07
  • 1
    It does indirectly indicate that it's for 2012, but the page is poor UI design. On pages where a function IS available in older versions, there's a drop down directly next to the version of the doc that you're reading. If you know this, then you know that it's for 2012 only. If you didn't know it, you end up in the same situation as Mitesh. – John Apr 09 '14 at 16:05

8 Answers8

109

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.

icc97
  • 11,395
  • 8
  • 76
  • 90
Lynn Langit
  • 4,030
  • 1
  • 23
  • 31
76

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
  • 29
    You can use `SELECT {fn concat ('foo', 'bar')};` in previous versions. Only accepts 2 parameters though. – Martin Smith May 11 '12 at 11:22
  • 6
    Or just use the `+` operator, as @lynn-langit mentions in her answer, which I at first totally missed because I only read the accepted answer... – Svish May 19 '15 at 13:11
  • 9
    @Svish `+` behaves differently, the results for `SELECT 'A' + 'B' + 'C'` vs `SELECT CONCAT('A', 'B', 'C')` vs `SELECT 'A' + 'B' + NULL` vs `SELECT CONCAT('A', 'B', NULL)` are `ABC`, `ABC`, `NULL`, `AB` – ta.speot.is May 19 '15 at 23:12
  • @ta.speot.is Good to know! Although in my case there was no cases of `NULL`, so `+` worked great :) – Svish May 21 '15 at 23:26
  • This answer would be much more useful if it contained the information that you have to use the plus-sign instead. The community confirms that opinion - compare the upvotes of the @LynnLangit's answer :) The OP asked *also* "how do I concatenate strings in SQL Server 2008 R2?" – Honza Zidek Aug 30 '19 at 14:17
52

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.

vasin1987
  • 1,962
  • 20
  • 26
  • 7
    TIP:Its worth noting that if a varchar size is not specified e.g. varchar(50), then sql will use the default of 30. If the variable / value being cast is more than the default, it will be truncated without raising an error. – Swifty Jan 18 '17 at 16:17
  • Idk why but took me a while to find a solution to this. That you. – Hozeis Jul 30 '21 at 17:38
23

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 + .....
podiluska
  • 50,950
  • 7
  • 98
  • 104
kuklei
  • 1,115
  • 11
  • 14
  • Thanks, using '' + f.columnName + '' within the column list works a treat! – Luke Jul 01 '13 at 12:36
  • 2
    @kuklei On my SQL server, `SELECT 'varchar(' + 5 + ')'` throws the error "Conversion failed when converting the nvarchar value 'varchar(' to data type int", so I guess your answer doesn't hold. – Alexander Apr 06 '16 at 09:13
8

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).

Troy Sheaffer
  • 101
  • 1
  • 6
3
(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))
miken32
  • 42,008
  • 16
  • 111
  • 154
Antonio Perez
  • 31
  • 1
3

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.

Gaurav Jeswani
  • 4,410
  • 6
  • 26
  • 47
M Faisal SE
  • 31
  • 1
0

You can use '+' between the strings that you want to concat like

SELECT string1 + string2

If one of those give conversion error like if one of the columns is an int column you should cast it before concatenating the columns like

SELECT (CONVERT(nvarchar, intColumn) + string2
Yağmur Başpınar
  • 1
  • 3