CODEWITHSUNDEEP
t-sqlsql-server-2008
user424134
user424134

Reputation: 532

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.

Upvotes: 0

Views: 367

Answers (4)

DavidEG
DavidEG

Reputation: 5957

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

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

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146603

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

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

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, '')

Upvotes: 0

Related Questions