Oracle SQL与PL/SQL实现按指定格式校验邮编字符串的方法

规则约定 #

基于测试数据集反推的格式占位符匹配规则：

• 格式字符串中的A：匹配单个大写英文字母（A-Z）
• 格式字符串中的#：匹配单个阿拉伯数字（0-9）
• 格式字符串中的其他字符（空格、横杠等）：作为固定字面量，必须和待校验邮编的对应位置完全一致
• 校验要求为完全匹配：待校验邮编长度、每个位置的字符类型必须和格式完全一致，无多余、缺失字符。

封装校验函数 #

CREATE OR REPLACE FUNCTION validate_zip_code(
    p_zip_code VARCHAR2,
    p_zip_format VARCHAR2
) RETURN BOOLEAN IS
    v_regex_pattern VARCHAR2(4000);
    v_format_char CHAR(1);
BEGIN
    -- 空值入参直接判定为不合法
    IF p_zip_code IS NULL OR p_zip_format IS NULL THEN
        RETURN FALSE;
    END IF;

    -- 逐字符解析格式规则，生成匹配正则
    v_regex_pattern := '';
    FOR i IN 1..LENGTH(p_zip_format) LOOP
        v_format_char := SUBSTR(p_zip_format, i, 1);
        CASE v_format_char
            WHEN 'A' THEN v_regex_pattern := v_regex_pattern || '[A-Z]';
            WHEN '#' THEN v_regex_pattern := v_regex_pattern || '[0-9]';
            ELSE 
                -- 其他字符转义为正则字面量，避免特殊字符干扰
                v_regex_pattern := v_regex_pattern || REGEXP_ESCAPE(v_format_char);
        END CASE;
    END LOOP;

    -- 添加前后锚点，确保全字符串完全匹配
    v_regex_pattern := '^' || v_regex_pattern || '$';

    -- 执行匹配校验
    RETURN REGEXP_LIKE(p_zip_code, v_regex_pattern);
END validate_zip_code;
/

配套SQL查询示例 #

PL/SQL布尔值无法直接在普通SQL中返回可读结果，需要做类型转换，配合测试数据集可使用如下查询输出TRUE/FALSE格式的校验结果：

WITH t AS (
SELECT 'AR' country,8 zip_code_length,'A####AAA' zip_code_format,'B6789CDF' zip_code FROM dual UNION ALL
SELECT 'AR' country,8 zip_code_length,'A####AAA' zip_code_format,'6789CDF' zip_code FROM dual UNION ALL
SELECT 'BR' country,9 zip_code_length,'#####-###' zip_code_format,'12345-678' zip_code FROM dual UNION ALL
SELECT 'BR' country,9 zip_code_length,'#####-###' zip_code_format,'BC345-678' zip_code FROM dual UNION ALL
SELECT 'CA' country,7 zip_code_length,'A#A #A#' zip_code_format,'B2C 8A9' zip_code  FROM dual UNION ALL
SELECT 'CA' country,7 zip_code_length,'A#A #A#' zip_code_format,'BbC CA9' zip_code  FROM dual UNION ALL
SELECT 'CH' country,4 zip_code_length,'####' zip_code_format,'1234' zip_code FROM dual UNION ALL
SELECT 'CH' country,4 zip_code_length,'####' zip_code_format,'123456' zip_code FROM dual UNION ALL
SELECT 'CZ' country,6 zip_code_length,'### ##' zip_code_format,'123 45' zip_code FROM dual UNION ALL
SELECT 'CZ' country,6 zip_code_length,'### ##' zip_code_format,'12345' zip_code FROM dual UNION ALL
SELECT 'EC' country,6 zip_code_length,'A####A' zip_code_format,'B3456C' zip_code FROM dual UNION ALL
SELECT 'EC' country,6 zip_code_length,'A####A' zip_code_format,'BBB56C' zip_code FROM dual UNION ALL
SELECT 'PL' country,6 zip_code_length,'##-###' zip_code_format,'12-345' zip_code FROM dual UNION ALL
SELECT 'PL' country,6 zip_code_length,'##-###' zip_code_format,'12-34567' zip_code FROM dual
)
SELECT 
    country,
    zip_code_length,
    zip_code_format,
    zip_code,
    CASE WHEN validate_zip_code(zip_code, zip_code_format) THEN 'TRUE' ELSE 'FALSE' END validation
FROM t;

校验结果 #

执行上述查询后输出与预期完全一致：

country zip_code_length zip_code_format zip_code    validation
AR      8               A####AAA        B6789CDF    TRUE
AR      8               A####AAA        6789CDF     FALSE
BR      9               #####-###       12345-678   TRUE
BR      9               #####-###       BC345-678   FALSE
CA      7               A#A #A#         B2C 8A9     TRUE
CA      7               A#A #A#         BbC CA9     FALSE
CH      4               ####            1234        TRUE
CH      4               ####            123456      FALSE
CZ      6               ### ##          123 45      TRUE
CZ      6               ### ##          12345       FALSE
EC      6               A####A          B3456C      TRUE
EC      6               A####A          BBB56C      FALSE
PL      6               ##-###          12-345      TRUE
PL      6               ##-###          12-34567    FALSE

兼容说明 #

• 若使用的Oracle版本低于11g，不支持REGEXP_ESCAPE函数，可直接将非A、非#的字符拼接至正则字符串即可（邮编常规格式中不会出现正则特殊元字符）
• 若需要支持小写英文字母校验，可将正则中的[A-Z]调整为[A-Za-z]即可。

内容的提问来源于stack exchange，提问作者Satya V