SQL Server中拆分PIDS列至GID与PACKAGEID列的技术求助

我的SQL Server表中有一列PIDS，数据示例如下：

需要将PIDS列拆分为GID和PACKAGEID两列，支持以下两种输出格式：

格式一：仅保留ID值 #

实现SQL #

SELECT
    -- 提取GID的纯值
    TRIM(SUBSTRING(PIDS, 
                   CHARINDEX('GID:', PIDS) + 4, 
                   CHARINDEX(',', PIDS, CHARINDEX('GID:', PIDS)) - CHARINDEX('GID:', PIDS) - 4)) AS GID,
    -- 提取PACKAGEID的纯值
    TRIM(SUBSTRING(PIDS, 
                   CHARINDEX('PACKAGEID:', PIDS) + 10, 
                   CASE 
                       WHEN CHARINDEX(',', PIDS, CHARINDEX('PACKAGEID:', PIDS)) > 0 
                       THEN CHARINDEX(',', PIDS, CHARINDEX('PACKAGEID:', PIDS)) - CHARINDEX('PACKAGEID:', PIDS) - 10 
                       ELSE LEN(PIDS) - CHARINDEX('PACKAGEID:', PIDS) - 9 
                   END)) AS PACKAGEID
FROM YourTableName;

说明：通过CHARINDEX定位关键词位置，SUBSTRING截取对应片段，TRIM去除多余空格，兼容GID和PACKAGEID顺序不固定的情况，以及最后一个字段无逗号的场景。

格式二：保留前缀（如GID:xxxx） #

实现SQL（方法一：拼接前缀） #

SELECT
    'GID:' + TRIM(SUBSTRING(PIDS, 
                   CHARINDEX('GID:', PIDS) + 4, 
                   CHARINDEX(',', PIDS, CHARINDEX('GID:', PIDS)) - CHARINDEX('GID:', PIDS) - 4)) AS GID,
    'PACKAGEID:' + TRIM(SUBSTRING(PIDS, 
                   CHARINDEX('PACKAGEID:', PIDS) + 10, 
                   CASE 
                       WHEN CHARINDEX(',', PIDS, CHARINDEX('PACKAGEID:', PIDS)) > 0 
                       THEN CHARINDEX(',', PIDS, CHARINDEX('PACKAGEID:', PIDS)) - CHARINDEX('PACKAGEID:', PIDS) - 10 
                       ELSE LEN(PIDS) - CHARINDEX('PACKAGEID:', PIDS) - 9 
                   END)) AS PACKAGEID
FROM YourTableName;

实现SQL（方法二：直接截取带前缀的片段） #

SELECT
    TRIM(SUBSTRING(PIDS, CHARINDEX('GID:', PIDS), 
                   CASE 
                       WHEN CHARINDEX(',', PIDS, CHARINDEX('GID:', PIDS)) > 0 
                       THEN CHARINDEX(',', PIDS, CHARINDEX('GID:', PIDS)) - CHARINDEX('GID:', PIDS) 
                       ELSE LEN(PIDS) - CHARINDEX('GID:', PIDS) + 1 
                   END)) AS GID,
    TRIM(SUBSTRING(PIDS, CHARINDEX('PACKAGEID:', PIDS), 
                   CASE 
                       WHEN CHARINDEX(',', PIDS, CHARINDEX('PACKAGEID:', PIDS)) > 0 
                       THEN CHARINDEX(',', PIDS, CHARINDEX('PACKAGEID:', PIDS)) - CHARINDEX('PACKAGEID:', PIDS) 
                       ELSE LEN(PIDS) - CHARINDEX('PACKAGEID:', PIDS) + 1 
                   END)) AS PACKAGEID
FROM YourTableName;

说明：方法二直接从关键词（GID:/PACKAGEID:）开始截取到逗号或结尾，再去除空格，更简洁地保留带前缀的格式。

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