Update Statement With WildCards

Question

I have the below update statement which works most of the time, but in the below example, the text White is not removed in line 2.

What needs to be changed in the below syntax so that the text from field2 is ALWAYS removed if it exists in field1 regardless of the text position in the string?

Declare @Table1 Table (field1 varchar(100), field2 varchar(100))

Insert Into @Table1 (field1, field2) Values
('Blue Green', 'Green'), ('Red White', 'Socks Are White')

UPDATE @Table1
SET field2 = REPLACE(field1,field2,'')
WHERE field1 like '%'+field2+'%';

Select * from @Table1

EDIT
Expected result after update is

'Blue Green', ''
'Red White', 'Socks Are'

Omit the word Green from field2 since it exists in field1 and omit the word White from field2 since it exists in field1

can you please display the expected result so we won't end up guessing cheers. — RoMEoMusTDiE, Dec 10 '17 at 20:04
Please explain why you think `White` should be removed from the second example row. — HABO, Dec 11 '17 at 00:38
@HABO - what I am after is to take each word from field1 and scan field2. if ANY word from field1 exists in field2 then remove that word from field2. — IcyPopTarts, Dec 11 '17 at 00:40
What parts of [`like`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql) and [`replace()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql) lead you to expect that behavior? Can you provide a complete and precise explanation of what you want, e.g. if the strings are `I live in a tent.` and `ten` what would your desired result be? — HABO, Dec 11 '17 at 03:43

score 1 · Answer 1 · answered Dec 11 '17 at 00:54

Problem is that REPLACE function is using a full replace. Below code will replace occurrence of "blue green" with "". But "Blue Green" string does not occur in the first string, so nothing is replaced.

REPLACE('Green', 'Blue Green', '')

For your solution you first need to split field1 into words. See this solution for splitting a string using a function. Alternatively, you can use CLR to call a .net function to split string.

Next, you can use a cursor to traverse the entire table and run REPLACE function once for every record returned by the above function, for every record in @Table.

Note that this would be much slower than the update statement you wrote initially because it is not using SET logic which SQL is very good for, but is iterating through table one record a time.

Update Statement With WildCards

1 Answers1