Clash Royale CLAN TAG

Sql Server: Remove list entry from the string of another list

I am sure that title is a bit confusing. Basically I have one table that has a column containing formation like "xx 123 Rg 43" and one that contains information like "Rg".. and if an entry from table 2's column is contained in the string contained in Table 1 then I need just that entry removed.. leaving us with "xx 123 43"

Currently I am using:

update [Table1]
set [Col1] = CASE
WHEN (select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0) is not null
THEN replace([Col1], (select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0), '')
ELSE [Col1]
END


And this works fine, but fails if the result of select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0 is more than 1 entry with this error:

select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


But I do need every match removed. Help!

1 Answer
1

You seems want :

update t1
set col1 = (case when exists (select 1
from t2
where charindex(t1.col2, t1.col1) > 0)
)
then stuff
else t1.col1
end)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.