Developing Matt

My Technical Journal

Concat this: NULL

leave a comment »

Concatenating fields with nulls is always a problem for new sql ‘queriers’.  In other words, if you combine the fields firstname with lastname (firstname + ‘ ‘ + lastname) to give you a nice visual look (“john doe”) you will get nulls if one of the fields contains a null value.  There are database settings you can apply to treat nulls differently (as spaces instead of nulls) but this has far reaching implications and could land you in line at the unemployment office.  Instead, just use the setting the concat_null_yields_null value to off during your query.  This will affect your instance only and will get you the results you need (assuming you want your concatenated results to not treat nulls as nulls):

set concat_null_yields_null off
select
firstname + ‘ ‘ + lastname from table

Advertisements

Written by matt

August 27, 2007 at 9:09 am

Posted in Sql Server, T-Sql

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

%d bloggers like this: