SQL Book

SQL Replace Function, purpose, syntax and common uses

The SQL Replace function enables us to look for a certain character phrase in a string and replace it with another character phrase. The updated string is then returned by the function.

The syntax for this string function is the same for SQL Server, Oracle and Microsoft Access. The syntax is as follows:

REPLACE(stringToLookIn, stringToMatch, replacementsString)

The syntax is fairly straight forward, the stringToMatch parameter is the character phrase that we want to replace, the replacementsString is the character phrase that will replace any occurence of the stringToMatch parameter.

The following first example finds any instance of the phrase 'MR' found in the Title column of the Customers table and replaces it with 'Mr' in the Title column. The second example replaces any instance of ' St.' in the StreetName column with ' Street'.

UPDATE Customers SET Title = Replace(Title, 'MR', 'Mr')

SELECT Replace(StreetName, ' St.', ' Street') As Street, City, ZipCode FROM Addresses

If the stringToMatch phrase occurs more than once in the string, then all instances of the phrase will be replaced with the replacement string:

SELECT REPLACE('I like SQL and I like Strings', 'like', 'love') As NewPhrase

-- returns: 
'I love SQL and I love Strings'

If no matches were found then the string is returned unaltered.

Conclusion

The Replace function syntax is the same on the major database systems in use today and enables us to easily replace specified phrases with a new specified phrase. We can see that the REPLACE function is of great use if you are wanting to 'clean' your data up into more standardised values that have been ignored when data is input into the database.