AZ93 Ответов: 1

Разбить поле на несколько строк - в SQL

Привет,

У меня есть таблица, в которой есть несколько комментариев в одном поле.
Например, 
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

И я хочу разбить его на разные строки Вот так

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 в качестве разделителя. Но с трудом разделяю его.

Что я уже пробовал:

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);

PIEBALDconsult

В SQL Server я делаю это с помощью табличной функции CLR, которая использует регулярное выражение.

AZ93

Я думаю, что функция разделения будет работать, но не знаю, как это сделать. Я хочу использовать и AM, и PM, но не знаю, как их использовать.

1 Ответов

Рейтинг:
1

Bryian Tan

Если разбить только одну строку, то можно попробовать следующий код. Он будет обрабатывать разделитель AM/PM

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

Выход:
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


если это для нескольких строк, то вот пример
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 $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'

SELECT F1.id
	,F1.data
	,O.splitdata
FROM (
	-- SELECT *, cast('<X>'+replace(F.data,'-','</X><X>')+'</X>' as XML) as xmlfilter from @t F
	SELECT *
		,CAST('<M>' + REPLACE(REPLACE(f.[data] COLLATE SQL_Latin1_General_CP1_CS_AS, ' PM', ' PM</M><M>'), ' AM', ' AM</M><M>') + '</M>' AS XML) AS xmlfilter
	FROM @t F
	) F1
CROSS APPLY (
	SELECT fdata.D.value('.', 'varchar(1000)') AS splitdata
	FROM f1.xmlfilter.nodes('M') AS fdata(D)
	) O
WHERE LEN(O.splitdata) > 0


Вывод (опущенный идентификатор, столбцы данных):
id	splitdata
1	CLAIM REFILED Rebecca Byrd 1/17/2018 3:17:53 PM
1	 PER BCBS, WE NEED TO REFILE THIS CLAIM Rebecca Byrd 1/10/2018 1:55:37 PM
1	 WAITING ON HOME PLAN TO REPLY Rebecca Byrd 1/2/2018 1:58:31 PM
1	 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


sql server - превращение строки, разделенной запятыми, в отдельные строки - переполнение стека[^]

Maciej Los

5ed!

Bryian Tan

спасибо :)