Remove row from variable based on matching rows

Question

I have a little problem and I know several solutions for this, but I don’t know how best (or the less dirty spaghetti path) to go.

I have a string variable and I need to use it in a similar expression.

So:

declare @a varchar(100) = 'my string to use as a join from' select * from table where column like '%' + @a + '%' 

But I don’t want any rows from the table containing the @a variable, I want any rows from the table to be contained in the @a variable, therefore:

 select * from table where @a like '%' + column + '%' 

Result:

 'my string' 'as a join from' 

But now I need to remove the matched lines from the @a variable. How can i do this?

(edit)

Expected Result:

 @a = ' to use ' 'my string' 'as a join from' 

Answer 1

You can change the @a value with each matched line in the list:

 select @a = replace(@a, MyColumn, '') from MyTable where @a like '%' + MyColumn + '%' 

Demo: http://www.sqlfiddle.com/#!3/187d6/5 (result: @a = ' to use ' )

---

If you want a result set, use the temp table to store the relevant rows:

 select MyColumn into #Temp from MyTable where @a like '%' + MyColumn + '%' -- @a = ' to use ' select @a = replace(@a, MyColumn, '') from #Temp -- returns result set of words that matched select * from #Temp drop table #Temp 

Demo: http://www.sqlfiddle.com/#!3/187d6/13