MSSQL Server 2012如何从LocationName列提取符合C+数字模式的记录

解决方案 #

你可以利用SQL Server自带的通配符范围匹配和PATINDEX函数来精准判断C后到第一个横杠之间的字符是否全为数字，根据你的使用场景有两种调整方式：

场景1：保留所有表记录，仅对符合规则的记录提取门店编码 #

修改CASE的判断条件即可，避免C开头但后续不是数字的记录被错误处理：

SELECT
    CASE 
        -- 规则：C开头+第二位起是数字+存在横杠+C到第一个横杠之间全为数字
        WHEN LocationName LIKE 'C[0-9]%' 
            AND CHARINDEX('-', LocationName) > 0
            AND PATINDEX('%[^0-9]%', SUBSTRING(LocationName, 2, CHARINDEX('-', LocationName) - 2)) = 0
        THEN SUBSTRING(LocationName, 1, CHARINDEX('-', LocationName) - 1)
        ELSE 'Unknown' + '-' + LocationName
    END AS storebusinesskey,
    * 
FROM [DBO].[Store]

注：这里把原来的SUBSTRING起始位置从0改成1是更符合SQL语义的写法，SQL Server中字符串下标从1开始，原来的写法刚好巧合生效，改后逻辑更清晰。

场景2：仅返回符合「C后接任意数量数字」规则的记录 #

如果不需要保留不符合规则的记录，直接加WHERE条件过滤即可：

SELECT
    SUBSTRING(LocationName, 1, CHARINDEX('-', LocationName) - 1) AS storebusinesskey,
    * 
FROM [DBO].[Store]
WHERE LocationName LIKE 'C[0-9]%' 
    AND CHARINDEX('-', LocationName) > 0
    AND PATINDEX('%[^0-9]%', SUBSTRING(LocationName, 2, CHARINDEX('-', LocationName) - 2)) = 0

逻辑说明 #

• [0-9]是SQL Server LIKE支持的范围通配符，匹配单个数字
• PATINDEX('%[^0-9]%', 字符串)会返回字符串中第一个非数字字符的位置，返回0就代表字符串全为数字，刚好用来校验C到横杠之间的字符是否符合要求
• 额外加了CHARINDEX('-', LocationName) > 0的判断，避免无横杠的字符串计算SUBSTRING时出现异常

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