SQL SERVER – Concat Function in SQL Server – SQL Concatenation

Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had a great time delivering the session. During the session, we talked about SQL Server 2012 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though, SQL Server is now in moving very fast and have proved many times a better enterprise solution, it does not have some basic functions. I naturally asked him for an example and he suggested CONCAT() which exists in MySQL and Oracle.

The answer is very simple – the equivalent function in SQL Server to CONCAT() is ‘+’ (plus operator without quotes).

Method 1: Concatenating two strings

SELECT 'FirstName' + ' ' + 'LastName' AS FullName

Method 2: Concatenating two Numbers

SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))

Method 3: Concatenating values of table columns

SELECT FirstName + ' ' + LastName
FROM AdventureWorks.Person.Contact

Well, this may look very simple but sometimes it is very difficult to find the information for simple things only.

Do you have any such example which you would like to share with the community?

Watch a quick video relevent to this subject:

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

41 thoughts on “SQL SERVER – Concat Function in SQL Server – SQL Concatenation

    • If you want catch a null value is correct but not all time, ‘concat’ says something to you??
      concat means union of two or more words, read correctly and then replay

      • Vinod actually raised a very important point. Most of the time users are using concat to concatonate 2 or more FIELDS. If any field contains a NULL value the concat will fail.

        Your reply is pretty embarassing TBH. Why when concatonating fields would you only want to capture null values ‘some of the time’? Sure all of the time is better as you know it will work every time!! :-)

    • hi sumit for split string you have to use function which is

      CREATE FUNCTION [dbo].[fnSplit](
      @sInputList VARCHAR(8000) — List of delimited items
      , @sDelimiter VARCHAR(8000) = ‘,’ — delimiter that separates items
      ) RETURNS @List TABLE (item VARCHAR(8000))
      BEGIN
      DECLARE @sItem VARCHAR(8000)
      WHILE CHARINDEX(@sDelimiter,@sInputList,0) 0
      BEGIN
      SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

      IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
      END
      IF LEN(@sInputList) > 0
      INSERT INTO @List SELECT @sInputList — Put the last item in
      RETURN
      END

  1. I always wonder why no one had add LIMIT() to the SQL Server like in MySQL.
    I the past (< SQL Server 2005) some wicked nested selects with TOP were necessary while from SQL Server 2005 on you can use row_number() to achieve a similar effect.

    Greetings,

    Michael

  2. “Well, this may look very simple but sometime it is very difficult to find the information for simple things only”

    Really? Just googling “Concat SQL Server” gave me a whole page of results, all of which mentioned using the “+” option.

  3. When concatenating more then two string values ,CONCAT() function is more difficult to use as compared to || signs in oracle or + sign in Sql Server.
    Like SELECT CONCAT(CONCAT(‘firt value’,’ second value’),’third value’) AS result
    But in Sql Server its so simple
    SELECT ‘firt value’+’ second value’+’ third value’

  4. Hi,
    I have been using the ‘+’ operator for a long time in SQL Server for concatenation purposes. One of recent comparisions i had to do was for the CONNECT BY clause available in ORACLE. I used CTE’s to get a similar effect as the CONNECT BY.

  5. Estou fazendo uma função e esbarrei no problema de não conseguir concatenar no from ou nas colunas….. Alguém pode me ajudar? Nas linhas destacadas estão o que quero fazer…

    alter function fcn_consultaTitulos(@result varchar(15), @nf varchar(9), @serie varchar(2), @emissao varchar(15), @cliLoja varchar(20), @parcela char(1), @empresa varchar(2))
    returns varchar(20)

    as begin

    declare @resultado varchar(20);

    declare @tabela varchar(10);

    set @tabela = ‘SE1’+@empresa+’0′;

    if(@result=’valor’)

    begin

    set @resultado =

    (SELECT top 1 E1_VALOR

    FROM @tabela SE1

    WHERE SE1.D_E_L_E_T_ ‘*’ AND SE1.E1_FILIAL=’00’

    AND SE1.E1_NUM =@nf

    AND SE1.E1_SERIE =@serie

    AND SE1.E1_EMISSAO=@emissao

    AND SE1.E1_CLIENTE+SE1.E1_LOJA =@cliLoja

    AND E1_PARCELA = @parcela

    ORDER BY SE1.E1_NUM, SE1.E1_SERIE,SE1.E1_PARCELA);

    end

    else

    if(@result=’dataVenc’)

    begin

    set @resultado =

    (SELECT top 1 E1_VENCREA

    FROM SE1+@empresa+0 SE1

    WHERE SE1.D_E_L_E_T_ ‘*’ AND SE1.E1_FILIAL=’00’

    AND SE1.E1_NUM =@nf

    AND SE1.E1_SERIE =@serie

    AND SE1.E1_EMISSAO=@emissao

    AND SE1.E1_CLIENTE+SE1.E1_LOJA =@cliLoja

    AND E1_PARCELA = @parcela

    ORDER BY SE1.E1_NUM, SE1.E1_SERIE,SE1.E1_PARCELA);

    end

    return @resultado

    end

    Desde já agradeço.
    Jardel

  6. Sorry pinaldave, I writed in portuguese…

    The question is that I need pass a param to set the number of the company to table, e.g:
    (company = 01)

    create function dbo.fcn_consultaTitulos(@companyvarchar(2))
    returns varchar(20)
    as begin
    declare @resultado varchar(20);
    if(@result=’valor’)
    begin
    set @resultado =
    (SELECT top 1 E1_VALOR
    FROM SE1+{@company}+0 SE1
    WHERE ……

  7. A funny story on concatenation in ANSI/ISO SQL. The correct syntax is || and not +. Overloading the + has been a problem for decades in T-SQL.

    The “pipe” symbol was submitted to ANSI X2H2 by Phil Shaw of IBM. Phil had been on the PL/I development team and borrowed this syntax.

    When we asked him if PL/I had any other good ideas we should look at for SQL, he went into a rant about how PL/I was the worst language ever designed. :)

  8. Suppose, we want to concatenate output returned in one column by query, what would be the best way?

    We have been using a CLR for such concatenation.

    Ex:
    SELECT master.dbo.CONCATENATE(name)
    FROM master.sys.tables
    ———
    spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_values,MSreplication_options

  9. Hi Pinal, how we can concatenate number and string?

    Example: If i want to print as filesize and MB next to file size like 120 MB for the below query.. how to do that?

    select (size*8) as FileSize from sys.database_files

  10. When doing yours and either column is null it just shows null for me, so I did:

    Select Case When (ForeName + ‘ ‘ + SurName) is null then (Select Case when (ForeName) is null then (SurName) else (ForeName) end) else (ForeName + ‘ ‘ + SurName) end as Fullname from customers

  11. hm, what if neither ‘+’ nor ‘Concat()’ are supported by SQL Server 2008 R2 using CF9? well, let me correct myself. ‘+’ is officially supported, however it can’t do the job because I’m trying to concat a text col with some user-updated content, and so the types don’t line up. well, so says the error msg returned from SQL. See below for exactly what happens.

    Anyone know how to get around this?

    When I do THIS:

    update tblNotes set sNotesAppend = sNotesAppend + ‘(user-updated content)’

    I got the error:
    ErrorCode = “402”
    Message = “[Macromedia][SQLServer JDBC Driver][SQLServer]The data types text and varchar are incompatible in the add operator.”
    SQLState = “HY000″

    So I turned to Googs which led me here and I see you mention Concat(), so I tried the same. However when I do it, when I do THIS instead:

    update tblNotes set sNotesAppend = Concat(sNotesAppend, Char(13), Char(10), Char(13), Char(10), GetDate(), Char(13), Char(10), ‘(user-updated content)’)

    I get the error:
    ErrorCode = “195”
    Message = “[Macromedia][SQLServer JDBC Driver][SQLServer]‘Concat’ is not a recognized built-in function name.”
    SQLState = “HY000″

    Fantastic! If I keep going this way I can waste the whole day instead of just parts of it! AWESOME!~

  12. Maybe cast the varchar as type text somehow?

    Anyway I could theoretically do a workaround where I handle in CF9 what SQL is giving me trouble with, but that’s a seriously huge workaround, cos:

    As the solution above provides, I can update many rows at once by doing it “where NotesID in (” & (csv list of IDs) & “)”. But the meanderingly long workaround would be, select each record, append the value in CF, then update that one record; it’d forbid the multiple updates, and hence be a lot more work. I’d like a simple elegant solution if possible, if SQL has one that is. Thanks for your time but an additional bonus thanks if anyone happens to know it:)

  13. OK also, not to come across like a complete idiot, I must point out that I later realized that what you were saying above was that Concat() does not exist in 2008 R2 and that the ‘+’ operator is the proper method. Regardless though, my question remains valid how one can properly (reliably) use ‘+’ to concatenate various entities with each other, especially a column’s current value for that record, dynamically. Thanks again

  14. I need to use something like the concat function in a subquery. The result set is a variable number of strings that should be concatenate to a single string. Because of the dynamic number of rows ‘+’ doesn’t work. So I am wondering why concat does not work although it is a documented function in Transact- SQL. Can anybody help?

  15. The following function can be a good example that I wrote

    ALTER FUNCTION [dbo].[fnConcatString]
    (
    @sInputList VARCHAR(8000) — List of delimited items
    , @sDelimiter VARCHAR(8000) = ‘,’ –delimiter that separates items
    )
    RETURNS VARCHAR(4000) –TABLE (item VARCHAR(8000))
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    DECLARE @FirstString VARCHAR(100)=”
    DECLARE @SecondString VARCHAR(100)
    DECLARE @Result VARCHAR(100)
    set @sInputList = dbo.udf_TitleCase(@sInputList)

    WHILE CHARINDEX(@sDelimiter,@sInputList,0)0
    BEGIN
    SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
    @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

    IF LEN(@sItem) > 0
    begin
    set @FirstString =@FirstString + @sItem
    end
    END
    IF LEN(@sInputList) > 0
    begin
    SET @SecondString = @sInputList — Put the last item in
    end
    Set @Result = @FirstString+”+@SecondString
    RETURN @Result
    END

  16. Pingback: SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video « SQL Server Journey with SQL Authority

  17. Hi, following is the mysql query

    DELETE FROM ATTRIBUTE_INSTANCE WHERE ATTRIBUTE_INSTANCE.ATTRIBUTE_NAME
    IN ( SELECT CONCAT(ATTRIBUTE_TEMPLATE.TEMPLATE_ID,’.’,ATTRIBUTE_TEMPLATE.ATTRIBUTE_NAME) FROM
    ATTRIBUTE_TEMPLATE, TEMP_ENTITY_TABLE WHERE
    ATTRIBUTE_TEMPLATE.TEMPLATE_ID=TEMP_ENTITY_TABLE.ENTITY_ID);
    I want to convert it to sql server specific query. please help me

    thanks

  18. just want to ask how to concatenate this one.

    (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database= ‘ + @ExcelFile + ‘, ‘select * from [Sheet1$]‘) AS A;

    Im’ getting errors

  19. just want to ask how to concatenate this one.

    (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database= ‘ + @ExcelFile + ‘, ‘select * from [Sheet1$]‘) AS A;

    Im’ getting errors

  20. Dear every one,
    I find that select @string = @string + columna +columnb from tabelx
    will produce memory leak if the tablex is quite big. Is there any way to this ?

  21. I have sql update queries stored in a column like select * from abc and now i want to execute these statements with a where condition. How do i concatenate condition with the select statement in database. i ried using cursor fetching values to variable and then
    @condition=@sql+’where 1=1′

  22. select f2+’ ‘+f3+’ ‘+f4 as ‘Name’ from [Table1] where ‘name’ in
    (select [full name] from [Table2])

    how to solve this?? i want to concat three columns and compare result with another column from Table 2. I am using sql server 2008 r2

    • SELECT f2+’ ‘+f3+’ ‘+f4 AS ‘Name’ FROM Table1
      WHERE CONCAT(f2,’ ‘,f3,’ ‘,f4) in
      (SELECT fullName FROM Table2)

      OR

      SELECT TB1.f2+’ ‘+TB1.f3+’ ‘+TB1.f4 AS ‘Name’ FROM Table1 AS TB1
      Inner Join Table2 AS TB2 ON TB2.fullName = CONCAT(TB1.f2,’ ‘,TB1.f3,’ ‘,TB1.f4)

  23. declare @a char (10)
    set @a=’01’
    print @a

    set @a=@a+’02’
    print @a

    …..it gives 01 both times but when i use ‘declare @a varchar (10)’ then it’s working fine…?

    • Hello Rahul Bansal!

      Datatype char has a fixed size, in your case 10. Your variable @a does not only contain ’01’, it contains also 8 whitespaces, too.
      If you now add ’02’ it won’t be added since @a has already the maximum amount of characters it can hold.

      The following piece of code will show the “effect”:
      declare
      @a char (10) = ’01’,
      @c char (12) = ’01’
      print @a
      print replace(@a, space(1), ‘.’) — show the whitespaces in order to make it clear
      print @c

      set @c = @a + ’02’ — will fit since variable c can hold up to 12 characters.
      set @a = cast(@a as char(8)) + ’02’

      print @a
      print @c

      Hope that helps.

      Greetings,

      Michael

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s