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: