Sunday, January 20, 2019

Use REPLACE multiple times on a single column

Using REPLACE in a statement allows us to update, or 'replace' something from a string with something else, like this:

       DECLARE @sample VARCHAR(15);
    SET @sample = 'Mister Jones'
    SELECT @sample [Old], REPLACE(@sample,'Mister','Mr.') [New];

Will give this back to you:

But what if there is more than one string in a single data value that you want to replace?  Maybe you need to replace 'Mister' and 'Misses' from the same column -- or even Miz!  You can run REPLACE more than once on a single column, in a single statement, simply by nesting them.  Like this:
CREATE TABLE #temp (DataValue VARCHAR(55))
INSERT #temp (DataValue)
VALUES ('Mister Jones'),
          ('missus Smith'),
          ('Miz Wilson');

       DataValue [Old],
       'Miz','Ms.') [New]
FROM #temp;

Gives you back a little something like this:

Important to note that this is not without overhead.  Putting the data in clean, or correcting the data values with an UPDATE would be more effective, but I understand we don't have a choice sometimes.  The nested REPLACE will let you run more than one REPLACE on a single data column.

See here for more details on REPLACE.   

1 comment: