SQL Replace Function – Swapping Words & Characters

Using the replace function in SQL can be useful to swap out words or characters with others. As an example, we often see delimiter characters end up in our database tables. This requires us to clean these characters out. We can do that in SQL using the replace function.

Reasons we may want to use REPLACE():

  • Swapping out misspelled words (i.e. ‘Carvan’ to ‘Caravan’)
  • Replacing invalid characters
  • Changing character delimiter patterns
  • Removing blanks or whitespace

Let’s first look at the syntax. The string searched will be replaced with the specified value. Also, the rest of the values will remain the same besides the string we searched for.

SELECT REPLACE(<column_name>, ‘string to find’, ‘replacement string’) AS NewColumn FROM Table;

Practical Examples of Using Replace

As previously mentioned, we can use SQL to replace words that are misspelled. In the below example we can see how we might execute this.

Misspelled Words
SQL Example of Replace Function

As a result, we can see the output in blue has fixed our misspelled name.

sql replace function output results
Removing Blanks & Whitespace

In the previous results, we can see row 7 has leading spaces. We can also use the SQL REPLACE function to remove spaces. Since we want to keep our renaming, we can embed the REPLACE within another REPLACE function.

sql replace removing blanks

This will result in removing our leading spaces from any words in our specified column.

removing blanks in sql results
Final Thoughts

Using the REPLACE() function in SQL has many uses. Everything from removing whitespace, swapping out bad characters to replacing complete words. Needless to say, it’s a very useful function. Anyone practicing ETL exercises will almost without a doubt use this function several times over.

Related Article: SQL TRIM – Removing Blanks & Whitespace