Recursive CTE to Replace Multiple Values



Create table tbla (CaseId int,	EventSeqNumber int,	EVNT_DESC_LINE varchar(2000))
Insert into tbla values(51,1500,'CAUSE SET FOR ^^^^^ ON ^^^^^ AT ^^^^^ OCLOCK ^^^^^.M.'),
(52, 1500, 'Example with just one ^^^^^ text key'), 
(53, 1500, 'Example with zero text keys')
 
CREATE TABLE tblb (TEXT_KEY1 varchar(20),	TEXT_LINE_NMBR int,	TEXT_LINE varchar(200))
INSERT tblb (TEXT_KEY1,	TEXT_LINE_NMBR,	TEXT_LINE) VALUES

('51000015001' ,1,'PROTECTIVE ORDER'),                                                       
('51000015002' ,1,'3/16/2000'),
('51000015003' ,1,'9:00'),
('51000015004' ,1,'A'),
('52000015001', 1, 'crazy implementation of a')


;with mycte as (
select caseid,EventSeqNumber, 0 as i, cast('' as varchar(200))  as sub, 0 as cidx, EVNT_DESC_LINE from tbla
union all
select cast(left(b.TEXT_KEY1,2) as int)
,cast( substring(b.TEXT_KEY1,7,4) as int)
,cast(right(b.TEXT_KEY1,1) as int)
,TEXT_LINE
,charindex('^^^^^',m.EVNT_DESC_LINE)
, Cast(stuff(m.EVNT_DESC_LINE, charindex('^^^^^',m.EVNT_DESC_LINE),5,TEXT_LINE) as varchar(2000)) as EVNT_DESC_LINE 
from tblb b join mycte m on right(b.TEXT_KEY1,1)=m.i+1  and m.Caseid=left(b.TEXT_KEY1,2) 
and m.EventSeqNumber=substring(b.TEXT_KEY1,7,4))


,mycte1 as (Select *,row_number() Over(Partition by caseid,EventSeqNumber Order by i DESC) rn from mycte)

Select caseid,EventSeqNumber,EVNT_DESC_LINE from mycte1 WHERE rn=1

drop table tbla,tblb
/*
caseid	EventSeqNumber	EVNT_DESC_LINE
51	1500	CAUSE SET FOR PROTECTIVE ORDER ON 3/16/2000 AT 9:00 OCLOCK A.M.
52	1500	Example with just one crazy implementation of a text key
53	1500	Example with zero text keys

*/

 

https://social.msdn.microsoft.com/Forums/en-US/0c9ac294-e9f8-4a01-89d1-57e2f417ad25/replacing-certain-string-with-values-from-another-table?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s