combining fields that may or may not have nulls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

combining fields that may or may not have nulls

Author

Topic

barb184
Starting Member

10 Posts

Posted - 2005-03-17 : 16:37:07

I am trying to write some sql that takes the contents from several fields and creates a new field with all of them. Problem is that if one of the fields that i am drawing data from is null than it just returns null for my new field. It does not ignore the null field and just return data from the fields that are not blank.

Here is the code that I wrote. It works fine as long as there is data in each of the fields... Any ideas would be greatly appreciated!

dbo.xProcess_2001.Family + ' ' + dbo.xProcess_2001.Type + ' ' + dbo.xProcess_2001.SubType AS ProcessAll

Barb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-17 : 16:40:41

You could use COALESCE on each of the columns or turn off CONCAT_NULL_YIELDS_NULL option in your stored proc.

Tara

barb184
Starting Member

10 Posts

Posted - 2005-03-17 : 16:45:19

Hmmm. I am very new at this... would you mind telling me exactly how to do that? Thank you!!

Barb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-17 : 16:48:54

Look them up in SQL Server Books Online. If you still have questions, please post a specific question.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-17 : 21:11:12

I am sure You have looked things up in BOL by now,
and You have something like this:
COALESCE(dbo.xProcess_2001.Family,'') + ' ' + COALESCE(dbo.xProcess_2001.Type,'') + ' ' + COALESCE(dbo.xProcess_2001.SubType,'') AS ProcessAll

If you design the database so that it does not allow NULL columns,
then you will not have this problem.

BOL - "A table should avoid nullable columns"