如何在TSQL中将单列文本解析为多列？解决Notes列解析难题

我有一段格式固定的文本数据存储在SQL表的note列中，格式如下：

Follow-up Date (use start date from OTO): 8-15-2024 
Contract Type (Competitive Win, Meter, New Product Line, Not Eligible): Meter 
Go Live Date (Confirm with TSE): 
Number of attempts to schedule (1, 2, 3, No reponse - collateral sent):  2  
Call completion/collateral sent date:  
Attendees (Meter Specialist, Meter Efficiency Partner, TSE): 
Notes:

需要将这段文本拆分为多列，每行的前缀（冒号前的内容）作为列名，冒号后的内容作为对应列的值。我用SUBSTRING()和CHARINDEX()编写了SQL语句，成功解析了除最后一列Notes外的所有列，请问该如何正确解析Notes列？

我的当前SQL语句如下：

SELECT chr_id,note,
      TRIM(SUBSTRING(note,
           CHARINDEX('Follow-up Date (use start date from OTO):',note)+41, 
           CHARINDEX('Contract Type (Competitive Win, Meter, New Product Line, Not Eligible):',note,
                     CHARINDEX('Follow-up Date (use start date from OTO):',note)+41)-
                     CHARINDEX('Follow-up Date (use start date from OTO):',note)-41)
          ) AS 'Follow-up Date',
      TRIM(
      SUBSTRING(
                 note,
                 CHARINDEX('Contract Type (Competitive Win, Meter, New Product Line, Not Eligible):',note)+71, 
                 CHARINDEX('Go Live Date (Confirm with TSE):',
                            note,CHARINDEX('Contract Type (Competitive Win, Meter, New Product Line, Not Eligible):',note)+71)-
                 CHARINDEX('Contract Type (Competitive Win, Meter, New Product Line, Not Eligible):',note)-
                 71)) AS 'Contract Type',
      TRIM(
      SUBSTRING(
                 note,
                 CHARINDEX('Go Live Date (Confirm with TSE):',note)+32, 
                 CHARINDEX('Number of attempts to schedule (1, 2, 3, No reponse - collateral sent):',
                            note,CHARINDEX('Go Live Date (Confirm with TSE):',note)+32)-
                 CHARINDEX('Go Live Date (Confirm with TSE):',note)-
                 32)) AS 'Go Live Date',
      TRIM(
      SUBSTRING(
                 note,
                 CHARINDEX('Number of attempts to schedule (1, 2, 3, No reponse - collateral sent):',note)+71, 
                 CHARINDEX('Call completion/collateral sent date:',
                            note,CHARINDEX('Number of attempts to schedule (1, 2, 3, No reponse - collateral sent):',note)+71)-
                 CHARINDEX('Number of attempts to schedule (1, 2, 3, No reponse - collateral sent):',note)-
                 71)) AS 'Number of attempts to schedule',
      TRIM(
      SUBSTRING(
                 note,
                 CHARINDEX('Call completion/collateral sent date:',note)+37, 
                 CHARINDEX('Attendees (Meter Specialist, Meter Efficiency Partner, TSE):',
                            note,CHARINDEX('Call completion/collateral sent date:',note)+37)-
                 CHARINDEX('Call completion/collateral sent date:',note)-
                 37)) AS 'Call completion/collateral sent date',
      TRIM(
      SUBSTRING(
                 note,
                 CHARINDEX('Attendees (Meter Specialist, Meter Efficiency Partner, TSE):',note)+60, 
                 CHARINDEX('Notes:',
                            note,CHARINDEX('Attendees (Meter Specialist, Meter Efficiency Partner, TSE):',note)+60)-
                 CHARINDEX('Attendees (Meter Specialist, Meter Efficiency Partner, TSE):',note)-
                 60)) AS 'Attendees',
   --   TRIM(
      --SUBSTRING(
   --              note,
            --   CHARINDEX('Notes:',note)+6, 
   --              LEN(note)-
            --   CHARINDEX('Notes:',note)-
      --           6)) AS 'Notes'

FROM table1

解析Notes列的方法 #

因为Notes是最后一列，没有后续的分隔标记，直接从Notes:之后的位置截取到字符串末尾即可。修正后的Notes列解析代码如下：

TRIM(SUBSTRING(note, CHARINDEX('Notes:', note) + 6, LEN(note) - CHARINDEX('Notes:', note) - 5)) AS 'Notes'

代码说明：

1. CHARINDEX('Notes:', note)定位到Notes:开头字符的位置，Notes:共6个字符，因此值的起始位置为该定位结果+6
2. 截取长度计算为LEN(note) - CHARINDEX('Notes:', note) - 5：总长度减去Notes:的起始位置，再减去5（对应Notes:最后一个字符的偏移量），得到的就是从起始位置到字符串末尾的字符数
3. TRIM()用于去除内容前后的空格、换行符等空白字符

将这段代码替换原SQL中注释的Notes部分，即可完成所有列的解析。

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