REPLACE Function in SQL Server

In this SQL Server tutorial, you will learn how to use the REPLACE function in SQL Server.

Where you will understand the workings of the REPLACE() function, and how to use it with a literal string, collation, and table columns.

Table of Contents

REPLACE Function in SQL Server

The REPLACE() function in SQL Server replaces the substring of the given string with the new string pattern. In simple words, if you want to replace the sub-part of the string with another string, then use the REPLACE() function.

This function is very useful where text manipulation is required such as data cleaning, formatting, or updating kind of tasks are involved.

The syntax is given below.

REPLACE(source_string, string_to_search, string_to_replace)

Where,

REPLACE(): It is the whole function itself that replaces the substring of the given string with a new string.
source_string: It is the actual string in which replacement is going to happen.
string_to_search: It is the string that you want to replace within the actual string.
string_to_replace: It is the new string that replaces the found substring (string_to_search) of the actual string.

Let’s see different examples by following the above syntax of the REPLACE() function.

REPLACE Function in SQL Server with Literal String

If you have only string which means literal string and want to replace the occurrence of the substring from that string based on the provided string pattern, then use the REPLACE() function.

For example, if you have a string ‘I live in USA’ and want to replace the word ‘USA’ with ‘United States of America’, then use the below query.

SELECT REPLACE('I live in USA', 'USA', 'United States of America');

REPLACE Function in SQL Server with Literal String

As you can see in the above output of the query, the string changed from ‘I live in USA’ to ‘I live in United States of America’, this happened because when you executed the above query it replaced the substring ‘USA’ with string ‘United States of America’.

This is how you can use the REPLACE() function with a literal string in SQL Server.

REPLACE() Function in SQL Server with Collation

The collation is a set of rules that determine how data can be stored and compared. But why collation is important?

It helps in sorting for example you have a list of names and want to sort the names in alphabetical order, the collation helps SQL Server to understand how to sort or order these names in the correct way.
Also, it helps in comparison of the strings, such as finding the name in a database, collation rules help the SQL server to know if the two strings are the same, even if they use different characters.

There are different types of collation rules, but explanations of these collations are not part of this tutorial. But here you need to understand how to replace the occurrence of the substring within a string with a new string based on the specified collations.

For example, let’s use the below query to replace the substring of the string without collation.

SELECT REPLACE('SQL Server Database', 'database', 'SSMS');

REPLACE() Function in SQL Server without Collation

If you look at the above output picture, it replaces the word ‘Database’ in the string ‘SQL Server Database’ with the new word ‘SSMS’ without any collation.

Here you need to notice that the substring ‘database’ that you want to search has a different case, and in the actual string it is different such as ‘Database’. So the REPLACE() function is case-insensitive, it treats ‘d’ and ‘D’ as the same.

Now use the collation with REPLACE() function to treat the ‘d’ and ‘D’ as different letters. For that use the below query.

SELECT REPLACE('SQL Server Database' COLLATE Latin1_General_CS_AS, 'database', 'SSMS');

REPLACE() Function in SQL Server with Collation

Here in the above output, the string ‘SQL Server Database’ remains unchanged as you can see.

So the REPLACE () function hasn’t found the substring ‘database’ in the given string because of the collation Latin1_General_CS_AS which tells the REPLACE() function to treat the letter ‘d’ and ‘D’ as the different letters or they are not the same.

In this case, the word ‘database’ is different from the word ‘Database’ in the string ‘SQL Server Database’, so the REPLACE() function hasn’t replaced the substring with a new string.

There are different collations with different rules that you can use for sorting or comparison of the string. If you use the collation Latin1_General_CI_AS instead of Latin1_General_CS_AS, then it replaces the substring with a new string as shown in the below query.

SELECT REPLACE('SQL Server Database' COLLATE Latin1_General_CI_AS, 'database', 'SSMS');

REPLACE() Function in SQL Server with different Collation

Now you can see the replacement that you wanted to replace. So here this time collation Latin1_General_CI_AS is based on the case-insensitive where lower and uppercase letters are the same.

This is how to use the REPLACE function in SQL Server with collation to replace the substring of the given string with a new string.

REPLACE Function in SQL Server with Table Column

Here, let’s see how to use the REPLACE() function with table columns, For example, you have a table CustomerData which is shown below.

REPLACE Function in SQL Server with Table CustomerData Column

Look at the above table column ProductDescription that contains the word ‘OldModel’, now you have to task to replace all the substring ‘OldModel’ with new string ‘NewModel’.

For that use the below query.

SELECT ProductDescription, REPLACE(ProductDescription, 'OldModel', 'NewModel') UpdatedProduct
FROM CustomerData;

After the execution of the above query, it replaces the word ‘OldModel’ with ‘NewModel’ for each product description in a ProductDescription column that you can see in the above output.

This is how to use the REPLACE function in SQL Server with table columns.

Conclusion

In this SQL Server tutorial, you learned how to replace the substring of the string with another string using the REPLACE() function in SQL Server. Also learned how to replace the substring of the string based on the collations, etc.