[RESOLVED] Nulls are messing me up

[MMock]

There is a column in my table called ad_ln2 (address line 2) which sometimes, probably most of the time, can be NULL. I have a stored procedure which runs a select based on search criteria and one of the things I search on is an address. Address is really the concatenation of: address line 1, address line 2, city state and zip. So let's say I wanted to get back all records whse city was Wolcott. I'd run this:

SELECT cstmr.cstmr_nm,
cstmr.cstmr_ik,
cstmr_addr.addrtyp_ik,
cstmr.usr_ik,
cstmr_addr.email,
cstmr_addr.cstmr_addr_ik,
cstmr_addr.mtr_nbr,
cstmr_addr.lst_nm,
cstmr_addr.Acct_Code,
cstmr_addr.acct_nbr,
cstmr_addr.ad_ln1, cstmr_addr.ad_ln2, cstmr_addr.cty, zp_cd,
cstmr_addr.ad_ln1 + cstmr_addr.ad_ln2 + cstmr_addr.cty + ' ' + zp_cd AS complete_address,
cstmr_addr.last_access_dt,
cstmr_addr.last_access_usr,
st.abbr
FROM cstmr
JOIN cstmr_addr ON cstmr.cstmr_ik = cstmr_addr.cstmr_ik
JOIN st on st.st_ik = cstmr_addr.st_ik
WHERE 1 = 1
AND cstmr_addr.ad_ln1 + cstmr_addr.ad_ln2 + cstmr_addr.cty + ' ' + zp_cd LIKE '%wolcott%'
order by cstmr.cstmr_ik, addrtyp_ik

I wasn't getting all the rows back that I expected and it turned out to be because of nulls in ad_ln2. If I updated the records to contain an emtpy string instead of a null, I got back all the rows I expected.

My question is, how do I code there WHERE clause to correctly handle null values?

Thanks (I hope I'm not asking you to do too much of my work for me).

[brucevde]

Use the IsNull or Coalesce statments to handle Null values when performing string concatenation. By default a String + a Null returns a Null.

cstmr_addr.ad_ln1 + IsNull(cstmr_addr.ad_ln2,'') + cstmr_addr.cty + ' ' + zp_cd LIKE '%wolcott%'

Alternatively, since this is a stored procedure you could run the following statement before the select.

Set Concat_Null_Yields_Null Off
Select...


[edit]I assumed your database is SQL Server...

[MMock]

Your assumption is correct.

Your answer is correct.

My stored procedure is now correct (I chose the alternative).

Thanks!!!!