Using REPLACE in a statement allows us to update, or 'replace' something from a string with something else, like this:
SET @sample = 'Mister Jones'
SELECT @sample [Old], REPLACE(@sample,'Mister','Mr.') [New];
Will give this back to you:
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.
No comments:
Post a Comment