For an instructor lead, in-depth look at learning SQL click below.
**
In this article, we’re going to learn how to replace part of a string in SQL. We often deal with situations where we need to modify the data in our tables, whether it’s correcting spelling errors, standardizing data, or even cleaning up messy, inconsistent inputs before we start an analysis. SQL provides a built-in function called REPLACE() to handle these instances of replacing part of a string.
Using the REPLACE() function
The REPLACE() function is relatively straightforward. It takes three arguments:
1. The original string
2. The substring to be replaced
3. The substring to replace it with
Here’s a basic example:
|
1 2 3 4 |
SELECT REPLACE('SQL Tutorial','Tutorial','Guide') AS NewString |
In this case, we’re replacing the word ‘Tutorial’ with ‘Guide’. The result is ‘SQL Guide’.
Replacing within columns
Most often though, we’re not just replacing within a simple string — we’re replacing within strings in a column of a table. Here’s how you might do that:
|
1 2 3 4 5 6 7 8 |
UPDATE your_table SET your_field = REPLACE(your_field, 'Old_string', 'New_string') WHERE your_field LIKE '%Old_string%' |
This tsql code first selects items where your_field contains ‘Old_string’ and then replaces ‘Old_string’ with ‘New_string’.
A Real World Example
Let’s say we had a table named ‘Orders’ and a column named ‘Comment’ that needed some of the values modified.
The table ‘Orders’:
|
1 2 3 |
SELECT * FROM Orders |
|
1 2 3 4 5 6 7 |
| OrderID | Product | Quantity | Comment | |---------|---------|----------|---------------------| | 1 | Apples | 20 | Good apples | | 2 | Oranges | 30 | Bad oranges | | 3 | Grapes | 15 | Not so good grapes | |
Now, suppose we want to replace all instances of the term ‘good’ with ‘excellent’ in the ‘Comment’ column. Here’s how you can do this:
|
1 2 3 4 5 6 7 8 |
UPDATE Orders SET Comment = REPLACE(Comment, 'good', 'excellent') WHERE Comment LIKE '%good%' |
This will change ‘Good apples’ to ‘Excellent apples’ and, ‘Not so good grapes’ to ‘Not so excellent grapes’.
Conclusion
Replacing part of a string in SQL is an incredibly handy skill to have, whether you’re a data analyst, data scientist, database administrator, or even a programmer dealing with databases. And luckily, with SQL’s REPLACE() function, it’s pretty simple to do.