0

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.

Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Oct 08 '21 at 20:29
  • I'm not following. You need a WHERE clause based on the value of a parameter? What's wrong with `postal_code = @postal_code`? You should include sample source data and your desired results here. – squillman Oct 08 '21 at 20:30
  • @squillman it seems that when a user enters 10023, it needs to match it to those with 10023, 10023-0001, 10023-000X and those who put in 100231001, etc. It has to match it to all of the possibilities . – YelizavetaYR Oct 08 '21 at 20:42

1 Answers1

0

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, ' ', '') + '%';
squillman
  • 13,363
  • 3
  • 41
  • 60
  • I don't think this would work for the following scenarios if someone enters this A1G 9Z9, I would need this A1G9Z9 as well as A1G 9Z9. It is not just a starting field. I am not sure how to encompass all scenarios. – YelizavetaYR Oct 08 '21 at 20:54
  • @YelizavetaYR Edited my answer – squillman Oct 08 '21 at 20:57
  • 1
    At some point you'll have to draw the line and determine when enough's enough. You can't possibly account for every instance of idiocy in user inputs... – squillman Oct 08 '21 at 20:58