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

ISNULL(P.CITY,'')
Techhead 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 Ratchev
http://www.SQLStudio.com

On Jun 4, 3:29 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
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?

Techhead (jorgenson.b (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx