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

Question

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})?$'))
                     )

Answer 1

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.