Today’s code editors and word processors come with a search and replace feature. It is helpful when you need to change a word or a group of words. We don’t want to throw away our written work for a minor problem or change.
The same thing applies to our data. Users will curse us if we tell them to edit what needs to be renamed. That’s why a search and replace feature is also available to replace texts written in our databases. Here the REPLACE command in SQL comes in.
The REPLACE syntax in SQL is as follows:
REPLACE(<original string>, <string to search>, <replacement string>)
Note that all parameters are required. The <original string> can be a string literal or a string result of an expression. In database tables, we usually pass a string column where we want the value to change. Meanwhile, <string to search> is a string within <original string> you want to search. Finally, you pass a <replacement string> that will replace <string to search>. Simple, enough, isn’t it?
But there can be some caveats. This way, you can find out how to avoid replacing the wrong words or how to avoid inserting duplicate records in the SQL Server. Or, maybe, not replacing the right words. Then messing up your data in the end.
Are you new to SQL REPLACE? Here’s a FREE cheat sheet for you. Just enter your email address below, and we’ll send it straight to your inbox.
Sounds cool? Let’s dig in.
[sendpulse-form id=”12251″]
1. SQL REPLACE Replaces Text for ALL Occurrences
That’s right. It sweeps your whole text with the string you want to search. Let me show it to you and explain the problem. Have a look at the below example:
SELECT REPLACE('know the unknown','know','seek');
-- OUTPUT: 'seek the unseekn. Surprise!
The know string exists twice, in the words know and unknown. If you are not careful, spelling mistakes will creep into your data. Instead, you can add space to search for complete words only. Here’s the modified code:
SELECT REPLACE('know the unknown','know ','seek ');
-- OUTPUT: 'seek the unknown'.
Much better? There can be other scenarios, but you get the point.
2. SQL REPLACE Can Remove Texts
Have you tried replacing a word with nothing in a text editor? Or maybe you want to remove duplicates in SQL? It also happens in SQL REPLACE. Here’s an example:
USE AdventureWorks
GO
SELECT
EmailAddress
,REPLACE(EmailAddress,'-','') AS NewEmailAddress
FROM person.EmailAddress;
The first column contains the original email address domain name. The second one removes the hyphen from the original domain name. See the screenshot below:
If you need to remove one or more characters from the original string, use an empty SQL string as a replacement. You can also delete duplicate records in SQL this way.
3. Collation Affects the Results of SQL REPLACE
When you install an SQL Server, a default collation is used. In turn, this will be used by your databases and table columns. Figure 2 shows the one I use:
On my laptop, I use SQL_Latin1_CP1_CI_AS. It is a case-insensitive collation. (See also CI in the collation name for case insensitive). Hence, the words Dog, dog, DOG, and dOg will be treated the same.
Let’s try it in action:
DECLARE @string VARCHAR(200) = 'Cats are great pets
and so easy to take care of. They make good companions.
Having a cat around is good for children.';
SELECT REPLACE(@string,'cat','dog');
And the result is:
The REPLACE feature found the word Cat in Cats in the first sentence and the word cat in the last sentence. It’s irrelevant if the letters are upper or lower case.
However, if we force a change in the collation to a case-sensitive one within REPLACE, what will happen? Here’s the code using COLLATE within REPLACE:
DECLARE @string VARCHAR(200) = 'Cats are great pets and so easy to take care of.
They make good companions. Having a cat around is good for children.';
-- force a change to a case-sensitive collation. '_CS_' for case-sensitive
SELECT REPLACE(@string COLLATE SQL_Latin1_General_CP1_CS_AS,'cat','dog');
And here’s the output:
You now see how collation affects the result. Cat and cat are now treated differently. The paragraph becomes confusing. So, the lesson is to use collation with caution.
4. You Can Nest SQL REPLACE
You can replace the words you already replaced by nesting the REPLACE function. Here’s an example:
USE AdventureWorks
GO
SELECT
definition
,REPLACE(REPLACE(definition,'CREATE PROCEDURE','ALTER PROCEDURE'),'BusinessEntityID','BusinessEntityNo')
FROM sys.sql_modules a
INNER JOIN sys.objects b ON a.object_id = b.object_id
WHERE a.definition LIKE '%BusinessEntityID%'
AND b.type = 'P';
The example above used a REPLACE within a REPLACE. That is to change CREATE PROCEDURE to ALTER PROCEDURE then BusinessEntityID to BusinessEntityNo.
The query will affect stored procedures that used the BusinessEntityID column. This is useful when you want to rename a column in a table and replace the affected database code. Stored procedure parameters are also affected, but you can choose to replace them to preserve the naming consistency.
Note that before executing the ALTER statements you need to check the changes that will be applied. You don’t want to mess things up, do you?
Meanwhile, nesting REPLACE can also fix the problem we have earlier on cats and dogs. Here’s the fix:
DECLARE @string VARCHAR(200) = 'Cats are great pets and so easy to take care of.
They make good companions. Having a cat around is good for children.';
SELECT REPLACE(REPLACE(@string COLLATE SQL_Latin1_General_CP1_CS_AS,'cat','dog') COLLATE SQL_Latin1_General_CP1_CS_AS,'Cat','Dog');
Check out the new result:
Notice also that the capitalization problem in Figure 3 is fixed too.
5. Use with UPDATE to Store Replaced Texts
So far, we have only used SQL REPLACE with a SELECT query. This will only allow us to see the output of REPLACE. To store its output, you also need to issue an UPDATE. Here’s an example:
USE AdventureWorks
GO
UPDATE Person.EmailAddress
SET EmailAddress= REPLACE(EmailAddress,'-','');
Here’s another example using MySQL involving the blog_posts table in WordPress. See how you can replace a private URL with a public one:
-- View the possible results
SELECT
guid
,REPLACE(guid,'http://localhost/techblog','https://sqltechblog.net') AS NewGUID
FROM blog_posts
WHERE post_date > '2020-10-01';
-- Update the blog_posts table by replacing localhost with a fictitious URL
UPDATE blog_posts
set guid = REPLACE(guid,'http://localhost/techblog','https://sqltechblog.net')
WHERE post_date > '2020-10-01';
The result set for the first statement above can be seen below using dbForge Studio for MySQL:
Conclusion
So far so good. You saw what REPLACE in SQL can do in action. It can give ideas when you need to change email addresses and URLs. if you rename a column in a table used in a stored procedure, view, or synonym.
Let’s have a recap:
- SQL REPLACE can replace text for ALL occurrences.
- It can also remove a part of the text.
- Collation settings can affect the output of REPLACE.
- You can nest the use of SQL REPLACE.
- Finally, use UPDATE to store the replaced texts.
If you like this post, please share it on your favorite social media. Let us also know what you think in the comments section below.
Tags: sql replace, sql server, t-sql Last modified: September 24, 2021
