SQL Server Replace

The name 'replace' defines itself that means it replaces something with another value. The replace function in SQL Server is used to replace all occurrences of a string or portion of the string (substring) with a new given string value. This article provides a complete overview of using the REPLACE() function for replacing all occurrences of a string with a new substring within a string.

Let us see the below example that explains the concept of replace function.

Here we'll look at an online ticket booking system. Suppose we want customers who are able to book tickets just for 'Shimla,' we'll have to change the current city status and all of its occurrences to 'Shimla.' In this case, we will use the replace function that enables us to replace each instance of the current city with 'Shimla.'

Syntax of REPLACE function

We can use the following syntax to replaces all occurrences or presence of the mentioned input string with the new string:

In this syntax,

string_rxpression: It is the input string within which the data will be replaced. It can be either character or binary data type.

substring: It is the string to be replaced. If the string is empty (''), it is returned unchanged.

string_replacement: It is the replacement string value that replaces the old string.

NOTE: We need to know that the replacement performed by the replace function is case-insensitive.

Return Type

• If one of the input arguments is of the nvarchar data type, this function returns nvarchar; otherwise, it will return varchar.

• If any of the arguments is NULL, the result is NULL.

Supported Versions

The replace function can support the following SQL Server versions:

SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2018, and SQL Server 2019.

Example

Let us understand how the replace() function works in SQL Server with some examples.

1. Using replace function with literal strings

Suppose we have a string 'It is a best coffee at the famous coffee shop.' And want to replace the substring 'coffee' with the 'tea', we can use the replace function for replacing the coffee with the tea in the given string as follows:

After executing this statement, we will get this output:

2. Using the COLLATE function with REPLACE

The replace function makes comparisons based on the input's collation. We may use COLLATE to apply an explicit collation to the input and perform a comparison in that collation.

The below example uses the case-sensitive collation function to make comparison and validate them inside the replace function:

After executing this statement, we will get this output:

The output does not change the input expression because case-sensitive collation fails to validate the input pattern.

We will use the same example with the case-insensitive collation function to make comparison and validate them inside the replace function:

After executing this statement, we will get this output:

The output shows the value are matched because case-insensitive collations validate the input pattern irrespective of cases.

3. Using replace function with table columns

Now, we will see how the replace() function works with table columns. First, we will create a table named 'Places' using the following statement:

Next, we will add data using the INSERT statement:

We will use the SELECT statement to verify the table:

Suppose we want to replace all occurrences of the character 'a' with 'A' in the 'City' column. We can do this by using the below statement with the replace function. Here we also provide the data values of 'City' as NOT NULL to replace the city column that has values.

After executing this statement, we will get the below output:

The below statement is another example to explain the replace function in the table:

After executing this statement, we could observe that the 'A' is replaced with the 'a'.