As the name indicates, CONCAT() will concatenate two or more strings. This is one of the two string functions introduced in SQL Server 2012, FORMAT() being the other one.
New? Wait. Concatenating isn’t new. It’s always been there. Yeah. you are right. No, concatenating isn’t new.Yes, CONCAT() function is new.
“+” is used to concatenate strings, prior to 2012. As you can see in the query below, two strings are being concatenated.
SELECT 'HELLO'+SPACE(1)+'WORLD'
It gets tricky when you deal with non varchar data, and null values.
SELECT 1+SPACE(1)+'World' ;
SELECT ‘John’+NULL+‘Doe’ ;
These queries don’t return expected results. To fix that, select query should contain logic to replace null and convert int to string. On the left side of the below image is the corrected query.
CONCAT(), as can be seen in the right side, simplifies this process. It concatenates 2 or more strings, and automatically (implicitly) converts null to blank space and integer to string.
One more example to better explain the use of CONCAT().
SELECT CONCAT(1,1) ConcatInt
, CONCAT('John',NULL,'Doe') ConcatNull
, CONCAT(1,SPACE(1),'John') ConcatIntString
Lets wrap up…
Concat() is clearly a simpler way to concatenate strings. It improves code readability and is one of my favorite T-SQL enhancements.
~Sam.
I really don’t get this. Maybe it’s the examples. Why would anybody concatenate a Null into a String in the first place ? Why use Null when uou mean ‘ ‘. Why use 1 when you mean ’1′ ?
Hi David,
Sorry – i should have been more specific with the examples, but please see my response below.
Why would anybody concatenate a Null into a String?
Imagine a customer table with the following columns: FirstName, MiddleName, and LastName. Lets say, for example, you want to concatenate all the columns and present a customer’s full name in a report. You should concatenate the three columns.Note that not all customers will have a middle name, it can be NULL.
Similarly, given a table with Street Number, Street Name, City, Zip Code as columns and Zip Code, Street Number are integers while the other columns are string data. You will want to concatenate int with string in this example to generate a full mailing address for any customer.
Hope this answers your question.
~Sam.
Pingback: SQL Server 2012 (“Denali”): T-SQL Enhancements | James Serra's Blog
hi
good post ,i have done with columns in sql server 2012
http://csharpektroncmssql.blogspot.com/2012/10/how-to-concatenate-strings-in-sql.html