将一个字段拆分为多个注释 - SQL

Question

我有一个在一个字段中有多个注释的表。 例如，

1) 索赔重新提交 丽贝卡·伯德 1/17/2018 3：17：53 PM 每个 CHGS，我们需要 重新提交此索赔 丽贝卡·伯德 1/10/2018 1：55：37 PM 在家等候 计划回复 丽贝卡·伯德 2018/1/2 1：58：31 PM A/R 发送给 CHGS 上 这种说法。作为重复项被拒绝，但只有一个索赔 ILINKBLUE为此DOS。测试 Byrd 12/29/2017 6：34：36 AM

2) HCRR 帐户 希拉·约翰逊 2017/9/28 7：37：55

3) 联系 VA VISN 与玛丽交谈，她说 pmt $18.32 是在 06/27/2014 - #675678- 她给了我国库 uh# 86-72-1141.我 联系了测试并与测试交谈 - 我给了她的号码，日期和PMT - 她发现 CK 和 PMT 为 0.00 年 6 月 27 日 2014 美元 - 她说这是 在布拉银行下兑现 - 跟踪 #0jgdjgkd。测试测试 2017-7-28 1：21：11 下午

我想像这样将其分成不同的行

1) CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM 
2) PER BCBS, WE NEED TO REFILE THIS CLAIM Rebecca Byrd 1/10/2018 1:55:37 PM 
3) WAITING ON HOME PLAN TO REPLY Rebecca Byrd 1/2/2018 1:58:31 PM 
4) A/R SENT TO BCBS ON THIS CLAIM. DENIED AS A DUPLICATE, BUT THERE WAS ONLY ONE CLAIM IN ILINKBLUE FOR THIS DOS. Rebecca Byrd 12/29/2017 6:34:36 AM 

所有评论都将以日期结尾。所以我会认为我可以使用 AM 或 PM 作为分隔符。但是很难分开。

Answer 1

首先：这个设计非常糟糕，你应该 - 如果有机会 - 真的改变这个！使用此方法将所有注释传输到相关的端表中。

你的问题不是很清楚，但我的魔水晶球心情很好，告诉我，你可能正在寻找这个：

CREATE FUNCTION dbo.SplitCommentOnTime(@str VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
WITH recCTE AS
(
SELECT 1 AS Pos
,LEFT(@str,PATINDEX('%:[0-5][0-9] [AP]M %',@str + ' ')+5) AS Part
,SUBSTRING(@str,PATINDEX('%:[0-5][0-9] [AP]M %',@str + ' ')+7,LEN(@str)) AS Remainder
UNION ALL
SELECT Pos+1
,LEFT(Remainder,PATINDEX('%:[0-5][0-9] [AP]M %',Remainder + ' ')+5) 
,SUBSTRING(Remainder,PATINDEX('%:[0-5][0-9] [AP]M %',Remainder + ' ')+7,LEN(@str)) 
FROM recCTE
WHERE PATINDEX('%:[0-5][0-9] [AP]M %',Remainder + ' ')>0
)
SELECT Pos,Part 
FROM recCTE;
GO
DECLARE @tbl TABLE(ID INT IDENTITY,Comment VARCHAR(MAX));
INSERT INTO @tbl VALUES
('CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM PER CHGS, WE NEED TO REFILE THIS CLAIM Rebecca Byrd 1/10/2018 1:55:37 PM WAITING ON HOME PLAN TO REPLY Rebecca Byrd 1/2/2018 1:58:31 PM A/R SENT TO CHGS ON THIS CLAIM. DENIED AS A DUPLICATE, BUT THERE WAS ONLY ONE CLAIM IN ILINKBLUE FOR THIS DOS. Test Byrd 12/29/2017 6:34:36 AM')
,('HCRR ACCOUNT Sheila Johnson 9/28/2017 7:37:55 AM')
,('Contacted VA VISN spoke with Mary she stated pmt $18.32 was made on 06/27/2014 - #675678- she gave me treasury uh# 86-72-1141. I contacted TEST and spoke with TEST - i gave her number, date and pmt - she found ck and pmt of $0.00 for date 06/27/2014- she said it was cashed under blah bank - trace #0jgdjgkd. Test Test 7/28/2017 1:21:11 PM')
SELECT *
FROM @tbl 
OUTER APPLY dbo.SplitCommentOnTime(Comment); 

GO
DROP FUNCTION dbo.SplitCommentOnTime;

结果

ID  Pos Part
1   1   CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM
1   2   PER CHGS, WE NEED TO REFILE THIS CLAIM Rebecca Byrd 1/10/2018 1:55:37 PM
1   3   WAITING ON HOME PLAN TO REPLY Rebecca Byrd 1/2/2018 1:58:31 PM
1   4   A/R SENT TO CHGS ON THIS CLAIM. DENIED AS A DUPLICATE, BUT THERE WAS ONLY ONE CLAIM IN ILINKBLUE FOR THIS DOS. Test Byrd 12/29/2017 6:34:36 AM
2   1   HCRR ACCOUNT Sheila Johnson 9/28/2017 7:37:55 AM
3   1   Contacted VA VISN spoke with Mary she stated pmt $18.32 was made on 06/27/2014 - #675678- she gave me treasury uh# 86-72-1141. I contacted TEST and spoke with TEST - i gave her number, date and pmt - she found ck and pmt of $0.00 for date 06/27/2014- she said it was cashed under blah bank - trace #0jgdjgkd. Test Test 7/28/2017 1:21:11 PM

一些解释

该函数使用递归 CTE 在字符串中跳转以查找模式。 模式是%:[0-5][0-9] [AP]M %。这意味着：一个双点，后跟 0-5 中的一个数字，然后是一个数字 0-9，一个空白，A 或 P，然后是一个 M 和一个空白。

剩下的就是LEFT和SUBSTRING

提示

拆分这些字符串怪物后，您可以使用REVERT()并搜索第三个空白。再次还原此片段，并将其转换为真实的DATETIME。这样，您将获得具有良好查询性能的端表。

您可能会以同样的方式让评论者摆脱困境......