SQL Server 2018：从多格式字符串提取指定长度的账户与主体ID

针对从格式杂乱的字符串字段中提取固定长度的账户ID（6位）和主体ID（9位）需求，这里提供基于SQL Server内置函数的实现方案，直接适配accounts/AccountId这类账户标识，以及类似格式的主体标识。

核心思路 #

利用PATINDEX定位标识字符串的位置，再结合SUBSTRING提取标识后固定长度的数字，最后通过TRY_CAST转换为整数类型（避免提取失败时抛出错误）。

示例代码 #

假设待处理的字符串字段名为RawString，表名为YourTable，主体标识为Subjects/SubjectId，可直接执行以下SQL：

SELECT
    -- 提取6位账户ID
    TRY_CAST(
        SUBSTRING(
            RawString,
            -- 定位标识后第一个数字的起始位置
            PATINDEX('%[Aa]ccount[sId][^0-9]*[0-9]%', RawString) + 
            PATINDEX('%[^0-9]%', SUBSTRING(RawString, PATINDEX('%[Aa]ccount[sId]%', RawString), 20)),
            6
        ) AS INT
    ) AS AccountID,
    -- 提取9位主体ID
    TRY_CAST(
        SUBSTRING(
            RawString,
            PATINDEX('%[Ss]ubject[sId][^0-9]*[0-9]%', RawString) + 
            PATINDEX('%[^0-9]%', SUBSTRING(RawString, PATINDEX('%[Ss]ubject[sId]%', RawString), 20)),
            9
        ) AS INT
    ) AS SubjectID
FROM YourTable

关键细节说明 #

1. 标识匹配：[Aa]ccount[sId]兼容大小写不同的标识（如accounts、AccountId、account等变体）
2. 分隔符兼容：[^0-9]*匹配标识与ID之间的任意非数字分隔符（空格、冒号、等号等都能适配）
3. 容错处理：TRY_CAST会在提取的内容不是有效整数时返回NULL，避免整个查询失败

自定义调整点 #

• 如果主体标识不是Subjects/SubjectId，只需把代码中[Ss]ubject[sId]替换为实际的主体标识模式（比如[Pp]rincipal[sId]）
• 若字符串中存在多个相同标识，可结合CHARINDEX指定从某个位置开始查找，或添加额外条件过滤重复匹配

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