sql server - SQL Compare varchar variable with another varchar variable -

i have table name lines has billid (int) , linereference (varchar(100) 2 columns. each billid has linereference value. however, value in linereference might not correct. have validate linereference variable has has correct reference value based on bill id.

example :

declare @icountref varchar(100) = 1,2,3  billid   linereference 100      1,2, 100      1,2,40,34 100      1 100      12 

from above table, need update linereference column.

billid   linereference 100      1,2 100      1,2 100      1 100      1 

i able update comparing variable : @icountref. linereference column should have values in @icountref. whatever values not there in @countref should removed. if there no matching values,then column should updated atleast number 1.

--create temp table , inserting data: declare @billsrefs table (     billid int,      linereference nvarchar(100) )  insert @billsrefs values (100,      '1,2,'), (100,      '1,2,40,34'), (100,      '1'), (100,      '12')  --declare variables declare @icountref varchar(100) = '1,2,3',         @xml xml, @ixml xml  --convert @icountref in xml select @ixml = cast('<b>' + replace(@icountref,',','</b><b>') + '</b>' xml)  --@ixml: --<b>1</b> --<b>2</b> --<b>3</b>  --convert table data in xml select @xml = ( select cast('<s id="'+linereference+'"><a>' + replace(linereference,',','</a><a>') + '</a></s>' xml)  @billsrefs xml path('') ) --@xml: --<s id="1,2,"> --  <a>1</a> --  <a>2</a> --  <a /> --</s> --<s id="1,2,40,34"> --  <a>1</a> --  <a>2</a> --  <a>40</a> --  <a>34</a> --</s> --<s id="1"> --  <a>1</a> --</s> --<s id="12"> --  <a>12</a> --</s>  --compare values temp table @icountref --we convert string xml - convert them intoi tables ;with final ( select distinct         t.v.value('../@id','nvarchar(100)') linereferenceold, -- @id take 'id="1,2,40,34"' xml above         case when s.g.value('.','int') null 1 else s.g.value('.','int') end linereference          -- '.' used take value inside closed tags @xml.nodes('/s/a') t(v) --we takes @xml (look above) , play nodes 's' (root each @id) , `a` left join @ixml.nodes('/b') s(g) --we takes @ixml has 'b' tags     on t.v.value('.','int') = s.g.value('.','int') --here join both xml `a` , `b`  tags )  --in final table this:  --linereferenceold  linereference --1,2,              2 --12                    1 --1,2,40,34         1 --1,2,40,34         2 --1                 1 --1,2,              1  --final select select c.billid,         stuff((select distinct  ','+cast(f.linereference nvarchar(10))         final f         c.linereference = f.linereferenceold         xml path('')),1,1,'') linereference @billsrefs c  

output:

billid  linereference 100     1,2 100     1,2 100     1 100     1 

if need update source table:

update c set linereference = stuff((select distinct  ','+cast(f.linereference nvarchar(10))         final f         c.linereference = f.linereferenceold         xml path('')),1,1,'') @billsrefs c