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 + ' ' + LastNameFROM 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?

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!! :-)

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.

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’

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.

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.

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

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

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?

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

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

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′

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’

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.