如何使用T-SQL解析PDF导出的断行文本并提取目标数据列

我使用C#从包含医疗服务商信息的PDF文件中提取了文本，将数据导入SQL表作为暂存，在解析数据时遇到了问题。

总共需要解析出6个列：
[NPI]、[Legal Name]、[Service Address]、[Effective Date]、[End Date]、[Comments]

[End Date]和[Comments]列为可空列，因此这两列可能存在数据也可能不存在。目前已经完成了[NPI]和[Legal Name]的解析，其余列暂未实现解析。

受PDF渲染逻辑影响，若单条记录内容无法在一行完整展示，信息会拆分到第2行或第3行。遇到这种情况时，需要将这些零散的记录片段与序列中的上一条主记录拼接。

示例如下：

第4条记录的值"Only"需要与第3条记录拼接，最终得到"Payment Suspension Only"，该值为对应行的[Comment]字段值。在解析为列之前，完整记录如下：

1414141414 All Health Community Health Center 7300 Santa Clause Avenue, Huntington Beach, CA 90255 
7/5/2019 Payment Suspension Only

另一个示例：第7、8条记录需要与第6条记录拼接，完整记录如下：

1212131415 Desert Cities Midwifery dba Desert Cities Midwifery and Women's Health Care 1224 E 
Wardlow Rd, Long Desert, CA 92881-7262 5/12/2021

最后一个同类示例为第21、22、23条记录，第22、23条需要与第21条拼接，结果如下：

1877808933 SQUARE OF TRUST INC 5755 Mountain Road Room 222 & C106 & Nurse's Office, Los 
Angeles, CA 90028 12/20/2013

测试数据构建SQL如下：

IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T
CREATE TABLE #T (ID INT,TXT VARCHAR(MAX))
INSERT #T
SELECT '1', 'NPI Legal Name Service Address Effective Date End Date Comments' UNION
SELECT '2', '1003889502 Hachi, Vendog 1000 Bontpeliar Dr., Suite 9, Modesto, CA 95555-1216 5/11/2019' UNION
SELECT '3', '1414141414 All Health Community Health Center 7300 Santa Clause Avenue, Huntington Beach, CA 90255 7/5/2019 Payment Suspension' UNION
SELECT '4', 'Only' UNION
SELECT '5', '1515151515 Desert Cities Midwifery dba  Desert Side Birth Center Long 1224 E Wardlow Rd, Long Desert, CA 90807-4833 5/12/2021' UNION
SELECT '6', '1212131415 Desert Cities Midwifery dba Desert Cities Midwifery and Women''s' UNION
SELECT '7', 'Health Care' UNION
SELECT '8', '23141 Moulton Pkwy, 108, Laguna Hills, CA 92653-1241 5/12/2021' UNION
SELECT '9', '1467590901 Desert Cities Midwifery dba Desert Cities Midwifery and Women''s' UNION
SELECT '10',    'Health Care' UNION
SELECT '11',    '32326 Coast Hwy, Suite G, Laguna Desert, CA 92651-6700 5/12/2021' UNION
SELECT '12',    'Restricted ProvidersNPI Legal Name Service Address Effective Date End Date Comments' UNION
SELECT '13',    '1313131313 Desert Cities Midwifery dba Desert Cities Midwifery and Women''s' UNION
SELECT '14',    'Health Care' UNION
SELECT '15',    '1224 E Wardlow Rd, Long Desert, CA 92881-7262 5/12/2021' UNION
SELECT '16',    '1423596601 Desert Cities Midwifery dba Desert Cities Midwifery and Women''s' UNION
SELECT '17',    'Health Care' UNION
SELECT '18',    '1222 New Rascal Rd, Riverside, CA 92000-7262 5/12/2021' UNION
SELECT '19',    '1356331573 Desert Cities Midwifery dba Desertside Birth Center 23141 Moulton Parkway, Suite 208, Laguna Hills, Ca 92653-1241 5/12/2021' UNION
SELECT '20',    '1003206681 Desert Cities Midwifery dba Desertside Birth Center 1353 Old Temescal Rd, Suite 203, Corona, CA 92881-7262 5/12/2021' UNION
SELECT '21',    '1877808933 SQUARE OF TRUST INC 5755 Mountain Road Room 222 & C106 & Nurse''s Office, Los Angeles, CA' UNION
SELECT '22',    '90028' UNION
SELECT '23',    '12/20/2013' UNION
SELECT '24',    '1902223666 SQUARE OF TRUST INC 651 Glenwood Road Rooms 2101 & 2115, Glendale, CA 91202 12/20/2013' UNION
SELECT '25',    '1566664665 SQUARE OF TRUST INC 928 N Avenue 53 Room 1 & 3, Los Angeles, CA 90022 12/20/2013' 

SELECT * FROM #T
ORDER BY ID

当前已实现的解析查询如下：

SELECT 
 [NPI] = CASE WHEN TXT LIKE '%[0-9]%' THEN SUBSTRING(TXT,1,10) END
,[Legal Name] = CASE WHEN SUBSTRING(TXT,12,1) LIKE '%[A-Z]%' THEN SUBSTRING(TXT,-1+PATINDEX('%[A-Z]%',TXT),PATINDEX('%[0-9]%', SUBSTRING(TXT,12,61))) END
,[Service Address] = ''
,[Effective Date] = ''
,[End Date] = ''
,[Comments] = ''
,*
FROM #T
ORDER BY ID

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