RSS

SQL Concatenation

24 Jul

As you have seen in the article SQL Concatenating: NULL problem you may have problems concatenating a set values if one of them contains a NULL value. Another problem might be if you concat values of different type.

SELECT CAST(1 as nvarchar)+'st TE'+null+'ST' --Results in NULL
SELECT 1+'st TE'+null+'ST' --results in InvalidCastException from varchar to int.

Beginning with the new SQL Server 2012 there is a new function called CONCAT that concatenates a bunch of objects. The cool feature is that there can be concatenated object of different type like NVARCHAR, INT, FLOAT, etc. You can also concatenate NULL without getting NULL as result. Additional you must not to convert the values to nvarchar prior passing as parameter to the CONCAT function.

SELECT CONCAT(1,'st TE',null,'ST') --Results in 1st TEST
Advertisements
 
Leave a comment

Posted by on July 24, 2012 in SQL

 

Tags: ,

Leave a Reply

Please log in using one of these methods to post your comment:

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

 
%d bloggers like this: