SQL server query for Concatenate column fail

You can query for combine columns using ‘+’ operator.

for example:
SELECT Col1 + Col2 AS CombineColumn

Question:
But this query will sometimes fail, how is that?

Answer:
This query will fail if either of the concatenating column contains ‘Null’ value.

Solution:
The isNull() Function can be used to check whether the column is Null.

Example With isNull() Function.
Select isnull(CAST(Customer.Contactid as nvarchar(10)),”) +’-‘+ isnull(Customer.Firstname,”) +’-‘+ isnull(Customer.LastName,”)
as [Full Name] from Customer

The first parameter is the column and the second parameter is what the method return if ‘Null’ was encountered.

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