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