将字段拆分为多行 - 在SQL中 [英] Split a field into multiple row - in SQL

问题描述

我有一个表在一个字段中有多个注释。

例如，

 1）索赔重新发布Rebecca Byrd 1/17/2018 3:17:53 PM PER BCBS，我们需要修改此索赔Rebecca Byrd 1/10/2018 1:55:37 PM等待回家计划回复Rebecca Byrd 1/2/2018 1:58:31 PM A / R向BCBS索赔此索赔。拒绝作为一个重复，但只有一个索赔在这个DOS的ILINKBLUE。 Rebecca Byrd 12/29/2017 6:34:36 AM 
 
 2）HCRR账户Sheila Johnson 9/28/2017 7:37:55 AM 
 
 3）联系VA VISN与玛丽谈到她说PMT $ 18.32是在2014年6月27日制作的 -  ck＃8659688-她给了我国库ph＃866-372-1141。我联系了财政部并与西莉亚交谈 - 我给了她的ck号码，日期和时间 - 她在2014年6月27日发现ck和pmt为123.90美元 - 她说它是在JP摩根大通下兑现的 - 追踪＃06272014101036158659688。 Sheila Johnson 7/28/2017 1:21:11 PM 

我想把它分成不同的行，这样就是

 1）索赔重新发布Rebecca Byrd 1/17/2018 3:17:53 PM 
 2）PER BCBS，我们需要修改此索赔Rebecca Byrd 1/10 / 2018 1:55:37 PM 
 3）等待回家计划回复Rebecca Byrd 1/2/2018 1:58:31 PM 
 4）A / R向本公司索赔。拒绝作为一个重复，但只有一个索赔在这个DOS的ILINKBLUE。 Rebecca Byrd 12/29/2017 6:34:36 AM 

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



我尝试过的事情：

  SELECT  A.comments，
 Split.a.value（' 。'，'  VARCHAR（max）'） AS 字符串
  FROM （ SELECT 评论，
 CAST（' < m>' + REPLACE（注释，'  PM'，' < span class =code-string>< m>'）+ ' '  AS  XML） AS 字符串
  FROM  [InstinxScript_Trai ning]。[dbo]。[BarComment_Example]） AS  A  CROSS   APPLY  String.nodes（'  / M'） AS 拆分（a）; 

解决方案

18.32于2014年6月27日制作 - ck＃ 8659688-她给了我国库电话＃866-372-1141。我联系了财政部并与西莉亚交谈 - 我给了她的ck号码，日期和时间 - 她发现了ck和pmt的

123.90，日期为06/27/2014-她说它已兑现摩根大通 - 追踪＃06272014101036158659688。 Sheila Johnson 7/28/2017 1:21:11 PM

我想把它分成不同的行，这样就是

 1）索赔重新发布Rebecca Byrd 1/17/2018 3:17:53 PM 
 2）PER BCBS，我们需要修改此索赔Rebecca Byrd 1/10 / 2018 1:55:37 PM 
 3）等待回家计划回复Rebecca Byrd 1/2/2018 1:58:31 PM 
 4）A / R向本公司索赔。拒绝作为一个重复，但只有一个索赔在这个DOS的ILINKBLUE。 Rebecca Byrd 12/29/2017 6:34:36 AM 

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



我尝试过的事情：

  SELECT  A.comments，
 Split.a.value（' 。'，'  VARCHAR（max）'） AS 字符串
  FROM （ SELECT 评论，
 CAST（' < m>' + REPLACE（注释，'  PM'，' < span class =code-string>< m>'）+ ' '  AS  XML） AS 字符串
  FROM  [InstinxScript_Trai ning]。[dbo]。[BarComment_Example]） AS  A  CROSS   APPLY  String.nodes（'  / M'） AS 拆分（a）; 

如果只拆分一行，则可以尝试以下代码。它将处理AM / PM定界符

  DECLARE   @ STR   NVARCHAR （MAX）= ' 索赔重新发布Rebecca Byrd 1/17/2018 3:17:53 PM PER BCBS，我们需要修改此索赔Rebecca Byrd 1/10/2018 1:55:37 PM等待回家计划回复Rebecca Byrd 1/2/2018 1:58:31 PM A / R在本索赔中向BCBS提出索赔。拒绝作为一个重复，但只有一个索赔在这个DOS的ILINKBLUE。 Rebecca Byrd 12/29/2017 6:34:36 AM' 
 
   -   将值转换为行 
  SELECT  Split.a.value（' 。'，'  VARCHAR（1000）'）' 评论' 
  FROM （
  SELECT  CAST（' < ; M>' + REPLACE（REPLACE（ @ STR ，'  PM'，'  PM< / M>< M>'），'  AM'，'  AM< / M>< M> '）+ ' < / M>'  AS  XML） AS 字符串
） AS  A 
  CROSS   APPLY  String.nodes（' < span class =code-string> / M'） AS 拆分（a）
  WHERE  LEN（Split.a.value（' 。'，'  VARCHAR（1000）'））>  0  

输出：

评论
索赔重新发布Rebecca Byrd 1/17/2018 3:17:53 PM 
 PER BCBS，我们需要修改此索赔Rebecca Byrd 1/10/2018 1:55:37 PM 
等待回家计划回复Rebecca Byrd 1/2/2018 1:58:31 PM 
 A / R向BCBS索赔此索赔。拒绝作为一个重复，但只有一个索赔在这个DOS的ILINKBLUE。 Rebecca Byrd 12/29/2017 6:34:36 AM 

如果是多行，这里是一个例子

  DECLARE   @ t  表（
 ID  INT   IDENTITY  
，数据 VARCHAR （ 4000 ）
）
 
  INSERT   INTO   @ t （数据）
  SELECT  '  CLAIM REFILED Rebecca Byrd 1/17/2018 3:17 ：下午53点每BCBS，我们需要修改此索赔Rebecca Byrd 1/10/2018 1:55:37 PM等待回家计划回复Rebecca Byrd 1/2/2018 1:58:31 PM A / R SENT TO BCBS在这个索赔上。拒绝作为一个重复，但只有一个索赔在这个DOS的ILINKBLUE。 Rebecca Byrd 12/29/2017 6:34:36 AM' 
 
  INSERT   INTO   @ t （数据）
  SELECT  '  HCRR ACCOUNT Sheila Johnson 9/28/2017 7:37:55 AM' 
 
  INSERT   INTO   @ t （数据）
  SELECT  ' 联系VA VISN与Mary谈过她说pmt 

Hi,

I have a table that have multiple comments in one field.
For eg,

1) CLAIM REFILED Rebecca Byrd  1/17/2018 3:17:53 PM  PER BCBS, 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 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

2) HCRR ACCOUNT Sheila Johnson  9/28/2017 7:37:55 AM

3) Contacted VA VISN spoke with Mary she stated pmt $18.32 was made on 06/27/2014 - ck #8659688- she gave me treasury ph# 866-372-1141. I contacted treasury and spoke with Celia - i gave her ck number, date and pmt - she found ck and pmt of $123.90 for date 06/27/2014- she said it was cashed under JP Morgan Chase - trace #06272014101036158659688. Sheila Johnson  7/28/2017 1:21:11 PM

And I want to split it into different rows like this

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

All the comments will end with a date. So I will thinking I can use AM or PM as a separator. But Having difficulty separating it.

What I have tried:

SELECT A.comments,  
     Split.a.value('.', 'VARCHAR(max)') AS String  
 FROM  (SELECT Comments,  
         CAST ('<m>' + REPLACE(Comments, 'PM', '<m>') + '' AS XML) AS String  
     FROM  [InstinxScript_Training].[dbo].[BarComment_Example]) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

解决方案

18.32 was made on 06/27/2014 - ck #8659688- she gave me treasury ph# 866-372-1141. I contacted treasury and spoke with Celia - i gave her ck number, date and pmt - she found ck and pmt of

123.90 for date 06/27/2014- she said it was cashed under JP Morgan Chase - trace #06272014101036158659688. Sheila Johnson 7/28/2017 1:21:11 PM

And I want to split it into different rows like this

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

All the comments will end with a date. So I will thinking I can use AM or PM as a separator. But Having difficulty separating it.

What I have tried:

SELECT A.comments,  
     Split.a.value('.', 'VARCHAR(max)') AS String  
 FROM  (SELECT Comments,  
         CAST ('<m>' + REPLACE(Comments, 'PM', '<m>') + '' AS XML) AS String  
     FROM  [InstinxScript_Training].[dbo].[BarComment_Example]) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

If only split one row, then you can try the following code. It will handle AM/PM deliminator

DECLARE @STR NVARCHAR(MAX) = 'CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM PER BCBS, 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 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'

-- Converts values to rows
SELECT Split.a.value('.', 'VARCHAR(1000)') 'Comments'
FROM (
	SELECT CAST('<M>' + REPLACE(REPLACE(@STR, ' PM ', ' PM</M><M>'), ' AM ', ' AM</M><M>') + '</M>' AS XML) AS String
	) AS A
CROSS APPLY String.nodes('/M') AS Split(a)
WHERE LEN(Split.a.value('.', 'VARCHAR(1000)')) > 0

Output:

Comments
CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM
PER BCBS, 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 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

if it for multiple row, here is an example

DECLARE @t TABLE (
	ID INT IDENTITY
	,data VARCHAR(4000)
	)

INSERT INTO @t (data)
SELECT 'CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM PER BCBS, 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 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'

INSERT INTO @t (data)
SELECT 'HCRR ACCOUNT Sheila Johnson 9/28/2017 7:37:55 AM'

INSERT INTO @t (data)
SELECT 'Contacted VA VISN spoke with Mary she stated pmt

这篇关于将字段拆分为多行 - 在SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！