需要在 SQL Server 存储过程中验证美国邮政编码 [英] Need to validate USA zip code in a SQL Server stored procedure

问题描述

我有一个带有 2 个可选参数的存储过程，如下所示.我需要验证可选参数@Zip 之一，即我需要确保用户在执行此存储过程时输入正确的美国邮政编码.我尝试在下面的存储过程中使用正则表达式，但它不起作用.请让我知道我怎样才能做到这一点?谢谢.

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

推荐答案

SQL Server 显然支持正则表达式，根据 这个 StackOverflow 发布，但看起来有一些极端的设置.

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

由于您的过程接受 @Zip 作为 INT 类型，您只需检查它是否介于 0 和 99,999 之间.但这与您尝试的正则表达式不一致，而且我建议使用 Aaron 上面指出的字符类型.

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.

如果 @Zip 是字符类型，则 SQL Server LIKE 足够健壮以对其进行验证.这将检查 ZIP 是 5 位数字还是 9 位数字或5 位数字加破折号加四位数字":

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]'

这将确保 ZIP 格式正确，但不能保证 ZIP 实际上被美国邮政服务认可为有效，也不能保证 ZIP 适合街道地址、城市和州.

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.

这篇关于需要在 SQL Server 存储过程中验证美国邮政编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！