CONCAT() string function–SQL Server 2012

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.

image

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.

About these ads

4 thoughts on “CONCAT() string function–SQL Server 2012

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

  2. Pingback: SQL Server 2012 (“Denali”): T-SQL Enhancements | James Serra's Blog

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