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

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

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
>

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.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') 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.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION

HTH,

Plamen Ratchev
http://www.SQLStudio.com

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

On Jun 4, 3:29 pm, "Plamen Ratchev" wrote:
> You can use COALESCE, something like this will do it:
>
> COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
> COALESCE(P.STATE, '') 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.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
> P.STATE, '') AS LOCATION
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

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?

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

Techhead (jorgenson.b@gmail.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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx