Nulls in columns additions when 1 or more column values is blank

[Techhead]

I am running into an issue when adding data from multiple columns into
one alias:

P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION

If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?

Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.

Thanks

[Seribus Dragon]

Re: Nulls in columns additions when 1 or more column values is blank

ISNULL(P.CITY,' ')
Techhead wrote:

I am running into an issue when adding data from multiple columns into
one alias:
>
P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION
>
If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?
>
Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.
>
Thanks
>

[Plamen Ratchev]

Re: Nulls in columns additions when 1 or more column values is blank

You can use COALESCE, something like this will do it:

COALESCE(P.ADDR 1, '') + ' - ' + COALESCE(P.CITY , '') + ', ' +
COALESCE(P.STAT E, '') AS LOCATION

Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:

COALESCE(P.ADDR 1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Techhead]

Re: Nulls in columns additions when 1 or more column values is blank

On Jun 4, 3:29 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

You can use COALESCE, something like this will do it:
>
COALESCE(P.ADDR 1, '') + ' - ' + COALESCE(P.CITY , '') + ', ' +
COALESCE(P.STAT E, '') AS LOCATION
>
Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:
>
COALESCE(P.ADDR 1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.c om

Somebody at work told me to use this:

SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END

It seems to work. Is this similar as to what is described above?

[Erland Sommarskog]

Re: Nulls in columns additions when 1 or more column values is blank

Techhead (jorgenson.b@gm ail.com) writes:

Somebody at work told me to use this:
>
SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END
>
It seems to work. Is this similar as to what is described above?

Yes, coalesce is a shortcut for the above. The nice thing with coalesce is
that it accept a list of values, and will return the first value that
is non-NULL.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx