SQL Server REPLACE: Explained

The SQL Server REPLACE function is a very simple system function that is built into Microsoft SQL Server. It is one of the best tools you can use for string manipulation in SQL Server.

In this very brief tutorial, we’ll discuss the SQL Server REPLACE function and how to use in in Microsoft SQL Server. We’ll discuss these topics:

1. The syntax of the REPLACE function
2. Examples of using REPLACE
3. Links

Without further ado…

1. The syntax of the REPLACE function

The syntax for the REPLACE function is very simple. You should understand that we use the REPLACE function within the SELECT list of a query. The syntax is as follows:

REPLACE(<input value>, <substring to look for within input value>, <replacement value>)

Let’s briefly talk about each part:

• <input value>: This will be the starting string that we want to search through. We’ll search through this string, looking for the substring that we want to replace with a different value.
• <substring to look for within input value>: This is the specific substring we’re looking for in our input value.
• <replacement value>: Once we find the substring, we’ll replace it with the value outlined for this argument.

Understand that REPLACE will return the updated string value. It will return the original input value with all instances of the substring replaced with the replacement value. We’ll look at some examples soon.

If any of the arguments are of data type NVARCHAR, the resulting string will also be NVARCHAR. Otherwise, the resulting string will be VARCHAR.

2. Examples of using REPLACE

The best way to understand REPLACE is to look at a few examples. First, let’s look at a simple example:

I am using the REPLACE function to replace all instances of the word ‘test’ in the string ‘this is a test’ with the words ‘wonderful query’. We can see that the output is the updated string. Simple enough, right?

If there are many instances of your <substring> value within your <input> value, the REPLACE function will replace them all with the <replacement> value. Take a look at this query that replaces all spaces with dashes:

If your <substring> value simply does not exist within your <input> value, the REPLACE function will simply return the original <input> value:

Using REPLACE in a query

We can use REPLACE in an actual query against a table. For example, take a look at this Orders table:

For example, let’s say that in the ProductOrdered column, we want to replace all instances of the word ‘Bench‘ with the words ‘Multi use table‘. Using REPLACE, we can do that:

Our <input> value is just the name of the column. For each row, SQL will look at the value in this column and use it in the REPLACE function. If the product contains the word ‘Bench’, it get’s replaced with ‘Multi use table’. Otherwise, the original value is returned!

Your arguments can be any type of expression

Really quick, I just want to point out that the arguments you pass to the REPLACE function can be any type of expression. In my examples, I have been using many string literals, but understand that your arguments can be any other type of expression, like the name of a column or the name of a variable, for example.

Here is an example of the same query we ran a moment ago, but this time we use variable names in the REPLACE function:

Easy peasy.

3. Links

Here is a link to the official Microsoft documentation on the SQL Server REPLACE function:

REPLACE (Transact-SQL)

Next Steps:

Leave a comment if you found this tutorial helpful! I hope you enjoyed this very brief tutorial on the SQL Server REPLACE function.

If you want to learn more about the different character string data types, you should check out some of my other tutorials. For example…

Do you know the difference between CHAR and VARCHAR?

I also mentioned the NVARCHAR data type. Do you know what that is? If not, check out the full tutorial:

SQL Server NVARCHAR Data Type: Explained

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!