Written by 10:07 Database development, Statements, Work with data

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

CodingSight - SQL Replace

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.

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

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

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

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

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

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 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: , , Last modified: September 24, 2021
Close