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

Accepted Answer

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.

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

Solution

Problem