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.