如何在my_column列中高效匹配美国州缩写？

阿华AIGC实验室

2026-6-14

高效匹配美国州缩写的SQL方案

核心思路

要精准匹配独立的美国州缩写（避免和其他单词连写的情况，如Azyme里的AZ），最优方案是结合州缩写列表与正则匹配，替代冗余的CHARINDEX+LIKE组合。这种方法更易维护，逻辑更清晰。

不同数据库实现示例

SQL Server 方案

利用PATINDEX实现词边界匹配，通过CTE定义完整的州缩写列表：

WITH StateAbbrs AS (
    SELECT Abbr FROM (VALUES 
        ('AL'),('AK'),('AZ'),('AR'),('CA'),('CO'),('CT'),('DE'),('FL'),('GA'),
        ('HI'),('ID'),('IL'),('IN'),('IA'),('KS'),('KY'),('LA'),('ME'),('MD'),
        ('MA'),('MI'),('MN'),('MS'),('MO'),('MT'),('NE'),('NV'),('NH'),('NJ'),
        ('NM'),('NY'),('NC'),('ND'),('OH'),('OK'),('OR'),('PA'),('RI'),('SC'),
        ('SD'),('TN'),('TX'),('UT'),('VT'),('VA'),('WA'),('WV'),('WI'),('WY')
    ) AS t(Abbr)
)
SELECT DISTINCT my_column
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM StateAbbrs
    -- 匹配：缩写前后为非大写字母/字符串首尾/单独缩写
    WHERE PATINDEX('%[^A-Z]' + Abbr + '[^A-Z]%', ' ' + my_column + ' ') > 0
       OR PATINDEX('^' + Abbr + '[^A-Z]%', my_column) > 0
       OR PATINDEX('%[^A-Z]' + Abbr + '$', my_column) > 0
       OR my_column = Abbr
);

PostgreSQL 方案

PostgreSQL支持原生单词边界正则（\m为词首，\M为词尾），写法更简洁：

WITH StateAbbrs AS (
    SELECT unnest(array[
        'AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA',
        'HI','ID','IL','IN','IA','KS','KY','LA','ME','MD',
        'MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ',
        'NM','NY','NC','ND','OH','OK','OR','PA','RI','SC',
        'SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'
    ]) AS abbr
)
SELECT DISTINCT my_column
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM StateAbbrs
    WHERE my_column ~* '\m' || abbr || '\M'
);

~*表示不区分大小写匹配，若需严格区分大小写改用~即可。

全文索引方案（可选）

若已创建全文索引，可尝试CONTAINS查询，但需注意分词逻辑：

SELECT my_column
FROM your_table
WHERE CONTAINS(my_column, 'AL OR AK OR AZ OR AR OR CA OR CO OR CT OR DE OR FL OR GA OR ' ||
                          'HI OR ID OR IL OR IN OR IA OR KS OR KY OR LA OR ME OR MD OR ' ||
                          'MA OR MI OR MN OR MS OR MO OR MT OR NE OR NV OR NH OR NJ OR ' ||
                          'NM OR NY OR NC OR ND OR OH OR OK OR OR OR PA OR RI OR SC OR ' ||
                          'SD OR TN OR TX OR UT OR VT OR VA OR WA OR WV OR WI OR WY');

注意：全文索引可能无法完全过滤缩写与其他单词连写的情况（如Hazy），需实际测试验证效果。