>Hello all,
>
>I need to write a SQL UPDATE statement that will replace a substring within a column with a blank. The substring to replace can be any value so I cannot search on this, but I do know that the value to replace is between known values. The column is a varchar that has descriptive values in it as follows:
>
>'Buy xxxxxx vSymbol @.....Sell yyyyyy vSymbol @....'
>
>The 'Buy' and 'Sell' are constant. The vSymbol is variable but will be the same in the column and there is another column in the table that has this value so it can be referenced in the update statement. The xxxxxx and yyyyyy are variable and might not have the same value but these are to be replaced with a blank (along with the vSymbol). The '@' sign is also constant. I want the end result column to be as follows:
>
>'Buy @...... Sell @'
>
>TIA

set @cString = 'Buy @ test another test Sell @'
set @FirstPosition = charindex('Buy ',@cString) + LEN('Buy ')
set @LastPosition = charindex('Sell ', @cString)

set @cNewString = replace(@cString, substring(@cString, @FirstPosition, @LastPosition - @FirstPosition),'')

Something like this, not tested.

You may also need to do it with CASE statement or only for charindex of first word and last word is in the string and FirstPosition less than LastPosition.