Select to pull Zip code based both foreign and domestic

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.

Answer

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, ' ', '') + '%';