SQL Server 2008 - Replace Text Values in Column with Values from Another Table -
i've tried flexing google-fu no avail here am! unfortunately cannot change these tables coming out of application have report out of.
in sql server 2008, i'm trying replace multiple values in 1 text string column (table 1) value table (table 2).
thanks in advance!!
table 1
id value ------------- 1 a1, a2, a3 2 a2, a3 3 a4 table 2
id value --------- a1 value1 a2 value2 a3 value3 a4 value4 desired output
id value ----------------------------- 1 value1, value2, value3 2 value2, value3 3 value4
i'm sorry solution in advance :) need though:
create table tablea( id int, string varchar(255) ) create table table2( id varchar , text varchar(255) ) insert tablea values(1,'a,b,c,d') insert tablea values(2,'e,f') insert table2 values('a', 'value1') insert table2 values('b', 'value2') insert table2 values('c', 'value3') insert table2 values('d', 'value4') insert table2 values('e', 'value5') insert table2 values('f', 'value6') select id, left(myconcat,len(myconcat)-1) ( select c.id, replace(replace(cast(cast('<i'+stuff((select * from( select a.[id] , split.a.value('.', 'varchar(1000)') string (select [id], cast ('<m>' + replace([string], ',', '</m><m>') + '</m>' xml) string tablea) cross apply string.nodes ('/m') split(a)) inner join table2 b on a.string = b.id a.id = c.id xml path ('') ),1,2,'') xml).query('/text') varchar(1000)),'<text>',''),'</text>',',') myconcat tablea c group c.id ) d
Comments
Post a Comment