RSS

SQL Concatenating: NULL problem

06 Feb

In a SELECT statement, after concatenating some columns (e.g. SELECT a+b+c), you may have the situation that there is no returning value for some row i.e. NULL. But if you select the columns seperately like (e.g. SELECT a,b,c) then you have some data. If you wondering why this happens here you get the answer. If you concatenate colums with other columns that contains the value NULL then the whole concatenated string is NULL.

E.g.

'a' + 'b' + NULL = NULL
'a' + 'b' + 'c' = abc

To avoid this you hava to use:

ISNULL(conditionToCheck,valueIfNull)
--or
COALESCE(columnThatMayContainNull,valueIfNull

E.g.

DECLARE @value varchar(10) = NULL;
'a' + 'b' + ISNULL(@value IS NULL,'') = 'ab'
'a' + 'b' + ISNULL(@value IS NULL,'c') = 'abc'
'a' + 'b' + COALESCE(@value ,'') = 'ab'
'a' + 'b' + COALESCE(@value ,'c') = 'abc'
Advertisements
 
1 Comment

Posted by on February 6, 2010 in SQL

 

Tags:

One response to “SQL Concatenating: NULL problem

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: