[RESOLVED] Nulls are messing me up

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).

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...

Your assumption is correct.

Your answer is correct.

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

Thanks!!!!