sql replace with variable value

Question

In SQL I would like to replace limit n per family with nothing, where n can be 1-100. how do i do that? I tried...

update t set field1 = Replace(field1,'Limit%per family','')

and nothing updated.

Answer 1

I would use SUBSTRING and CHARINDEX together to accomplish this:

DECLARE @str CHAR(100)
SELECT @str = 'other text Limit 4 per family some more text'

SELECT REPLACE(@str, SUBSTRING(@str,CHARINDEX('Limit', @str), CHARINDEX('family', @str)-6),'')

For me, it returns: "other text some more text"

All you have to do is plug in the last select statement to your update call and replace @str with field1