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. As a result, this requires us to clean these characters out and 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
As a result, we can see the output in blue has fixed our misspelled name.
Removing Blanks & Whitespace
Additionally, we can see in the above example that 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.
This will result in removing our leading spaces from any words in our specified column.
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.