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:
DROP TABLE #temp;
CREATE TABLE #temp (DataValue VARCHAR(55))
INSERT #temp (DataValue)
VALUES ('Mister Jones'),
          ('missus Smith'),
          ('Miz Wilson');

SELECT
       DataValue [Old],
       REPLACE(
              REPLACE(
                     REPLACE(
                     DataValue,'Mister','Mr.'),
              'missus','Mrs.'),
       '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.   



2 comments: