Naomi,

Thanks for you help. You gave me enough information that gave me a kick start to the solution. It is a little brainy and hard to read and probably more complicated than it needs to be. Hopefully I will not have to look at it again.

Here it is:

UPDATE #tempProfit 
	set profitDesc = stuff(cast(profitdesc as varchar(8000)), patindex('%Sell %' + rtrim(instrumentSymbol) + '%', profitdesc) + 4, 
	charindex(rtrim(instrumentSymbol) + ' @', profitdesc, patindex('%Sell %' + rtrim(instrumentSymbol) + '%', profitdesc) + 4) -
	(patindex('%Sell %' + rtrim(instrumentSymbol) + '%', profitdesc) + 4), ' ')

UPDATE #tempProfit 
	set profitDesc = stuff(cast(profitdesc as varchar(8000)), patindex('%Buy %' + rtrim(instrumentSymbol) + '%', profitdesc) + 4, 
	charindex(rtrim(instrumentSymbol) + ' @', profitdesc, patindex('%Buy %' + rtrim(instrumentSymbol) + '%', profitdesc) + 4) -
	(patindex('%Buy %' + rtrim(instrumentSymbol) + '%', profitdesc) + 4), ' ')

>>Hello all,
>

>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.