如何用SQL自动提取字符串中产品状态及索引并实现查询复用

• 现有SQL表中包含MyString列，该列存储的字符串里每个字符对应产品生命周期的不同状态
• 需求：编写SQL代码，自动从MyString中提取产品的当前状态、上一状态及其对应的索引位置
• 目前已实现单值场景的模拟代码，但希望将查询自动化，支持多条记录复用，不确定是否可行

用户提供的单值模拟代码 #

DECLARE @MyString nvarchar(100) = 'AAABBC'

SELECT      
SUBSTRING(@MyString,1,1) as first_status,
CHARINDEX('b',@MyString) AS second_status_index,
SUBSTRING(@MyString,4,1) as second_status,
CHARINDEX('c',@MyString) AS third_status_index;

预期输出 #

自动化解决方案（支持多条记录） #

针对多条记录的场景，不能依赖硬编码的字符位置，需要通过字符串拆分+分组的方式自动识别连续状态段，以下是实现代码（假设表名为ProductStatus，包含ID和MyString列）：

WITH SplitChars AS (
    SELECT 
        ID,
        MyString,
        SUBSTRING(MyString, n, 1) AS StatusChar,
        n AS Position
    FROM ProductStatus
    CROSS APPLY (
        SELECT TOP (LEN(MyString)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM master..spt_values
    ) AS Numbers
),
StatusGroups AS (
    SELECT 
        ID,
        StatusChar,
        MIN(Position) AS StatusStartPosition,
        MAX(Position) AS StatusEndPosition,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(Position)) AS StatusSeq
    FROM SplitChars
    GROUP BY ID, StatusChar, 
        -- 按连续相同字符分组的关键逻辑
        Position - ROW_NUMBER() OVER (PARTITION BY ID, StatusChar ORDER BY Position)
)
SELECT 
    sg1.StatusChar AS first_status,
    sg2.StatusStartPosition AS second_status_index,
    sg2.StatusChar AS second_status,
    sg3.StatusStartPosition AS third_status_index
FROM StatusGroups sg1
LEFT JOIN StatusGroups sg2 ON sg1.ID = sg2.ID AND sg2.StatusSeq = 2
LEFT JOIN StatusGroups sg3 ON sg1.ID = sg3.ID AND sg3.StatusSeq = 3
WHERE sg1.StatusSeq = 1;

代码说明 #

1. SplitChars：将每条记录的MyString拆分为单个字符及其对应的位置索引
2. StatusGroups：对连续相同的字符进行分组，计算每个状态段的起始/结束位置，并按出现顺序为状态编号
3. 最终查询通过自连接获取第1、2、3个状态的信息，自动适配所有记录的状态提取需求

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