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

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQLAuthority News – What’s New in SQL Server “Denali”
Next Post
SQL SERVER – Challenge – Puzzle – Usage of FAST Hint

Related Posts

47 Comments. Leave new

  • Abhishek Chakladar
    November 8, 2012 11:50 am

    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

    Reply
  • Abhishek Chakladar
    November 8, 2012 11:56 am

    Hi guyes

    please follow the following LOC of my above post

    CHARINDEX(@sDelimiter,@sInputList,0) != 0

    the less than () has been encoded I guess by the site so that (!=) is not coming you can also use “”

    Reply
  • 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

    Reply
  • How to concatinate variable of datatype varchar and function getdate() while alter table query?

    Reply
  • 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

    Reply
  • trytrtrytrytyt
    January 25, 2013 3:28 pm

    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

    Reply
  • 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 ?

    Reply
  • 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′

    Reply
  • MSSQL 2012 now has a concat function.

    Reply
  • 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

    Reply
    • vishal mehta
      May 29, 2014 12:55 pm

      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)

      Reply
  • 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…?

    Reply
    • 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

      Reply
  • CONCAT function in SQL Server 2012 allows TEXT types to be joined, but theSQL Server 2008 R2 and earlier versions using the ” + ” method does not from my experience. That’s the problem I’ve run into.

    Reply
  • SELECT CAST(NULL AS VARCHAR(10)) + ‘R’ + CAST(2 AS VARCHAR(10))

    Can you check this result.
    I am getting null result.

    Reply
    • Because you are using NULL in the CAST function. Anything you add to NULL always gives you NULL result.

      Reply
  • SET CONCAT_NULL_YIELDS_NULL OFF;
    SET CONCAT_NULL_YIELDS_NULL ON;
    I found this solution but it is good to use?

    Reply

Leave a Reply

Menu