CODEWITHSUNDEEP
sql-serverstored-procedures
user1164164
user1164164

Reputation: 77

Need to validate USA zip code in a SQL Server stored procedure

I have a stored procedure with 2 optional parameters as below. I need to validate one of the optional parameter @Zip i.e. I need to make sure that the user is entering correct USA zip code, when he is executing this stored procedure. I tried using a regular expressions as in the below stored procedure, but it did not work. Please let me know how can I accomplish this? Thank you.

                     CREATE PROCEDURE usp_GetDetails
                     (
                       @Name varchar(255) = null, @Zip int = null
                      )
                     as
                     begin
         SELECT DISTINCT
         [Name],[Address1],[Address2],[City],[Zip]
         FROM    AddressTable
         WHERE 
             ( @Name IS NULL
                      OR AddressTable.[Name] LIKE '%' + @Name + '%'
                     )
                AND  ( @Zip IS NULL
                     OR AddressTable.[Zip] = (RegexMatch (@Zip, '^\d{5}(\-\d{4})?$'))
                     )

Upvotes: 5

Views: 10103

Answers (2)

Jon vB
Jon vB

Reputation: 71

Borrowing from the excellent answer from Ed Gibbs - if you are going to compare the zip code to a reference table - you need to ensure there are leading zero's stored, and you really only care about the first five digits - you can use something like this:

LEFT JOIN DimZipCode dz on REPLICATE('0',5-LEN(LEFT(fia.ZipCode,5))) + LEFT(fia.ZipCode,5)  = dz.ZipCode

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

SQL Server apparently supports regular expressions according to this StackOverflow posting, but it looks like there's some extreme setup.

Since your procedure accepts @Zip as an INT type, you only have to check that it's between 0 and 99,999. But that's at odds with your attempted regex, plus I'd recommend using a character type as Aaron points out above.

If @Zip is a character type, the SQL Server LIKE is robust enough to validate it. This will check if the ZIP is 5 digits or 9 digits or "5 digits plus dash plus four digits":

IF   @Zip LIKE '[0-9][0-9][0-9][0-9][0-9]'
  OR @Zip LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  OR @Zip LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

This will ensure the ZIP is well-formed, but it won't guarantee that the ZIP is actually recognized as valid by the US Postal Service, nor will it guarantee that the ZIP is appropriate to the street address, city and state.

Upvotes: 13

Related Questions