Replace in TSql

I want to开发者_如何学运维 update a row which is in format like this ABC - DEF - GHI - ABD,

Using a cmd like this UPDATE tbl SET col = Replace(col,@var, '') WHERE Pk=something. How can I remove the trailing or leading hyphens? For exmaple if @var is GHI, after replace, row value should be ABC-DEF-ABD or if @var is ABC then value should be DEF - GHI - ABD.

Add an extra ' - ' to the end of the string, replace @var with the dash appended, then strip the trailing ' - ' when you're done.

UPDATE tbl
    SET col = LEFT(REPLACE(col + ' - ', @var+' - ', ''), LEN(col)-6)
    WHERE Pk = something

First of all, agree completely with comment above.. Change teh data sschema to not store delimited lists of values in database column.

But, if this is not possible, Try this:

Update Table Set 
   colName = Replace(Replace(colName, '- ' + @var, ''), @var + ' -', '')
Where pk = @pk

How about:

UPDATE tbl SET col = replace(replace(col, @var + ' – ', ''), ' – ' + @var, '')

The inner replace replaces all middle and “leading” instances, and the outer replace catches any “final” instances.

If there are single instances, e.g. GHI, add a further and outermost call:

UPDATE tbl SET col = replace(replace(replace(col, @var + ' – ', ''), ' – ' + @var, ''), @var, '')

You can use:

-- 1: replace(col,@var, '') => your replacement
-- 2: replace(#1, ' - ', '-') => remove spaces
-- 3: replace(#2, '- ', '') => remove first hyphen and space
-- 4: replace(#3, ' -', '') => remove last hyphen and space

UPDATE tbl
   SET col = replace(replace(replace(replace(col,@var, ''), ' - ', '-'), '- ', ''), ' -', '')        
 WHERE Pk=something