If you frequently work with data, you will occasionally need to replace a part of the values in a column for some reason. In our case, we recently had some data that was loaded into a table from a file source and all the values had an unwanted leading and trailing tab character.
We needed to remove these tabs and preferably without resourcing and reloading the data. The solution was to use the REPLACE SQL function.
This is the form of the UPDATE statement that uses the REPLACE function
Update TABLENAME Set COLUMN_TO_BE_UPDATED = REPLACE(COLUMN_TO_BE_UPDATED, ‘Char_or_String_to_replace_in_column_values’, ‘Char_or_String_to_replace_it_with’);
Here is an example that replaces the long street names in the ADDRESS column with their abbreviated corresponding name:
update EMPLOYEE set ADDRESS = REPLACE(ADDRESS, ‘Street’, ‘St’ ), ADDRESS = REPLACE(ADDRESS, ‘Avenue’, ‘Ave’ ), ADDRESS = REPLACE(CITY, ‘Circle’, ‘Cir’ ) where COUNTRY = ‘United States’;
To solve the issue we had, the SQL statement below was used to update the EMPLOYEE column values by replacing the tab character with NULL – as shown below.
Important Note: Before making any major updates to a table, it’s a best practice to back up the table first.
update EMPLOYEE_EXPENSE_DATA set EMPLOYEE = REPLACE(EMPLOYEE, ‘ ‘, NULL) where EMPLOYEE like ‘ %’;
Important Note: Verify your update before you commit the changes.
We repeated this SQL for each column that had the “bad” (unwanted leading and trailing tabs) values.
Thanks for reading! I hope you found this information useful.
