i have table thousands of rows of data similar this.
note ---- lb mn im 12 18 20 cin # ew80851r tj ified kgm nteal icne cinac12345t cannot locate ncr last verified 06 05 14 cin number ty56478p front od 00612 last verified cin#ad89521y me side c 05 05 14 spc 0 verified pd imb cin pu12301r last verified pc po con fc d verified 02/29/2016 no copy cin#fg62301f each row contains word cin followed several characters.
for example: cin # ew80851r, cinac12345t, cin number ty56478p, cin#ad89521y, cin pu12301r, cin#fg62301f
how write query return only:
note ---- cinew80851r cinac12345t cinty56478p cinad89521y cinpu12301r cinfg62301f
with little xml, string parser , cross apply, can process entire data set.
you'll note added second record
declare @yourtable table (id int,note varchar(max)) insert @yourtable values (1,'lb mn im 12 18 20 cin # ew80851r tj ified kgm nteal icne cinac12345t cannot locate ncr last verified 06 05 14 cin number ty56478p front od 00612 last verified cin#ad89521y me side c 05 05 14 spc 0 verified pd imb cin pu12301r last verified pc po con fc d verified 02/29/2016 no copy cin#fg62301f'), (2,'l tj kgm teal icne last verified 06 05 14 front od 00612 last verified cin#zz89256y me side c 05 05 14 spc 0 verified pd imb cin zzpu12301r last verified pc po con fc d verified 02/29/2016 no copy cin#zz62301f') -- create mapping/normaization table declare @map table (mapseq int,mapfrom varchar(25),mapto varchar(25)) insert @map values (1,char(13),' '),(2,char(10),' '),(3,' cin number ',' cin'),(4,' cin # ',' cin'),(5,' cin#',' cin'),(6,' cin ',' cin') -- convert base data xml declare @xml xml,@string varchar(max) set @xml = (select keyid=id,string=+' '+note+' ' @yourtable xml raw) -- convert xml varchar(max) , apply global search & replace select @string = cast(@xml varchar(max)) select @string = replace(@string,mapfrom,mapto) @map order mapseq -- convert xml select @xml = cast(@string xml) -- generate final results select a.id ,cin = b.key_value ( select id = t.col.value('@keyid', 'int') ,newstring = t.col.value('@string', 'varchar(max)') @xml.nodes('/row') t (col) ) cross apply (select * [dbo].[udf-str-parse](a.newstring,' ') key_value 'cin%') b returns
id cin 1 cinew80851r 1 cinac12345t 1 cinty56478p 1 cinad89521y 1 cinpu12301r 1 cinfg62301f 2 cinzz89256y << dummy record 2 cinzzpu12301r << dummy record 2 cinzz62301f << dummy record the udf
create function [dbo].[udf-str-parse] (@string varchar(max),@delimeter varchar(10)) --usage: select * [dbo].[udf-str-parse]('dog,cat,house,car',',') -- select * [dbo].[udf-str-parse]('john cappelletti here',' ') -- select * [dbo].[udf-str-parse]('id26,id46|id658,id967','|') -- select * [dbo].[udf-str-parse]('hello world. it. is. . raining.today','.') returns @returntable table (key_ps int identity(1,1), key_value varchar(max)) begin declare @xml xml;set @xml = cast('<x>' + replace(@string,@delimeter,'</x><x>')+'</x>' xml) insert @returntable select key_value = ltrim(rtrim(string.value('.', 'varchar(max)'))) @xml.nodes('x') t(string) return end 
Comments
Post a Comment