I need to write a T-SQL procedure where the parameter is zip code. We've got the following parameter declared.
declare @postal_code varchar(10)
Sample data in table:
postal_codes |
---|
NULL |
46383 |
074523632 |
B4H34 |
113601419 |
ZH/8600 |
A1G 9Z9 |
WN73R |
Wd3 3he |
89136 |
etc. We've got a variety of addresses some having no postal code for certain foreign countries to some having your standard 5 digit or 10 digit US postal codes.
I need to code the query in some way to say:
select *
from table_name
where postal_code = @postal_code
My initial code was this:
select *
from table_name
where (@postal_code is null or
left(ad.postal_code, 5) = @postal_code)
but this doesn't work for anything other then 5 digit ones, then I tried using 10 digit ones but the 5 digit ones didn't match. Do I need to strip spaces or other characters? I tried searching and there is a variety of solutions but I need something that works for all kinds of zip codes foreign and domestic.
Based on your last comment it sounds like you need a "starts with" query. Try this
SELECT *
FROM table_name
WHERE REPLACE(postal_code, ' ', '') LIKE REPLACE(@postal_code, ' ', '') + '%';