SQL Server REPLACE Function

Summary: in this tutorial, you will learn how to use the SQL Server REPLACE() function to replace all occurrences of a substring by a new substring within a string.

SQL Server REPLACE function overview

To replace all occurrences of a substring within a string with a new substring, you use the REPLACE() function as follows:

REPLACE(input_string, substring, new_substring);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• input_string is any string expression to be searched.
• substring is the substring to be replaced.
• new_substring is the replacement string.

The REPLACE() function returns a new string in which all occurrences of the substring
are replaced by the new_substring. It returns NULL if any argument is NULL.

SQL Server REPLACE() function examples

Let’s take some examples of using the REPLACE() function to understand how it works.

A) Using REPLACE() function with literal strings

The following example uses the REPLACE() function to replace the tea with the coffee in the string 'It is a good tea at the famous tea store.':

SELECT 
    REPLACE(
        'It is a good tea at the famous tea store.', 
        'tea', 
        'coffee'
    ) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-------------
It is a good coffee at the famous coffee store.

(1 row affected)

As you can see from the output, all occurrences of tea were replaced with coffee.

B) Using REPLACE() function with table columns

In this example, we will use the sales.customers table from the sample database:

This example calls the REPLACE() function twice to format the phone number in a new format:

SELECT    
	first_name, 
	last_name, 
	phone, 
	REPLACE(REPLACE(phone, '(', ''), ')', '') phone_formatted
FROM    
	sales.customers
WHERE phone IS NOT NULL
ORDER BY 
	first_name, 
	last_name;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

How it works.

• The first call REPLACE(phone, '(', '') replaces the character ‘(‘ in the phone number by a space e.g., (916) 381-6003 -> 916) 381-6003
• The second call reuses the result of the first call and replaces the character ‘)’ by a space e.g., 916) 381-6003 -> 916 381-6003.

C) Using REPLACE() function to correct data in tables

The REPLACE() function is often used to correct data in a table. For example, replacing the outdated link with the new one.

The following is the syntax:

UPDATE 
    table_name
SET
    column_name = REPLACE(column_name, 'old_string','new_string')
WHERE
    condition;
Code language: SQL (Structured Query Language) (sql)

For example, to change the city code of the phone numbers from 916 to 917, you use the following statement:

UPDATE
    sales.customers
SET
    phone = REPLACE(phone,'(916)','(917)')
WHERE
    phone IS NOT NULL;
Code language: SQL (Structured Query Language) (sql)

Notice that you should back up the table before performing replacements.

In this tutorial, you have learned how to use the SQL Server REPLACE() function to replace all occurrences of a substring, within a string, with a new substring.