Top 5 Facts to Find and Replace SQL Texts in SQL Server with REPLACE Function

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 SQL REPLACE comes in.

CodingSight - SQL Replace

REPLACE Syntax in SQL Server

The syntax 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 avoid replacing the wrong words. 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.

CodingSight - SQL REPLACE: The Top 5 Facts to Find and Replace SQL Texts

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? 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:

adventure-works.com becomes adventureworks.com after using REPLACE. SQL REPLACE can remove texts.
Figure 1. adventure-works.com becomes adventureworks.com after using REPLACE. SQL REPLACE can remove texts.

If you need to remove one or more characters from the original string, use an empty string as a replacement.

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:

SQL_Latin1_CP1_CI_AS is my SQL Server’s collation
Figure 2. SQL_Latin1_CP1_CI_AS is my SQL Server’s collation.

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 word ‘cat’ has been replaced by ‘dog’ with SQL REPLACE. 'Cat' and 'cat' have been treated the same
Figure 3. The word ‘cat’ has been replaced by ‘dog’ with SQL REPLACE. ‘Cat’ and ‘cat’ have been treated the same.

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:

The change to a case-sensitive collation made the word ‘Cat’ and ‘cat’ seem different. ‘Cat’ was not replaced by SQL REPLACE
Figure 4. The change to a case-sensitive collation made the word ‘Cat’ and ‘cat’ seem different. ‘Cat’ was not replaced by SQL REPLACE.

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 Function

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.

Using REPLACE within REPLACE results in a modified code.
Figure 5. Using REPLACE within REPLACE results in a modified code.

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:

A fixed result after nesting REPLACE with a case-sensitive collation.
Figure 6. A fixed result after nesting REPLACE with a case-sensitive collation.

Notice also that the capitalization problem in Figure 3 is fixed too.

5. Use SQL UPDATE with REPLACE to Store Replaced Texts

So far, we 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:

Using REPLACE in MySQL for changing URLs in blog_posts table in WordPress.
Figure 7. Using REPLACE in MySQL for changing URLs in blog_posts table in WordPress.

Conclusion

So far so good. You saw what SQL REPLACE 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.

Read Also:

How to avoid inserting duplicate records in SQL server;
How to delete duplicate records in SQL Server

Edwin Sanchez

Edwin Sanchez

Software developer and project manager with a total of 20+ years of software development. His most recent technology preferences include C#, SQL Server BI Stack, Power BI, and Sharepoint. Edwin combines his technical knowledge with his most recent content writing skills to help new breed of technology enthusiasts.

Leave a Reply

Your email address will not be published. Required fields are marked *