SQL REPLACE Function Use and Examples

Overview

The REPLACE SQL function is used to replace a string or substring of a string with another string in a T-SQL script, SELECT statement, UPDATE statement, SQL query, or stored procedure in a Microsoft SQL database.

Explanation

Syntax

REPLACE(expression, stringToReplace, stringReplacement)

Parameters

expression – Original string or expression to be replaced completely or partially, it can be varchar, nvarchar or binary data types.
stringToReplace – String value to be replaced, it can be varchar, nvarchar or binary data types.
stringReplacement – Replacement string, it can be varchar, nvarchar or binary data types.

Simple SQL REPLACE Function Example

The following example will replace the word “World” with “MSSQLTIPS” to create a new string.

SELECT REPLACE('Hello World','World','MSSQLTIPS') as output

NULL Values in SQL REPLACE Function

If some of the arguments are NULL, the output will be NULL. To replace NULL values, you will need to use the ISNULL function instead.

SELECT REPLACE(NULL,NULL,'None') as output

Remove a Word with SQL REPLACE Function

The following example will remove the word “World” from “Hello World”.

SELECT REPLACE('Hello World','World','') as output

SELECT REPLACE('Hello World','World','') as output

Use SQL REPLACE with Numeric Values

The following example shows that numeric numbers can be replaced with this function. This example replaces the number 1 with 5.

SELECT REPLACE(11112233,1,5) as output

SQL Server replace function with numbers

Working with Collations and SQL REPLACE Function

The following example replaces the word “World” using the Albanian_BIN collection collation and is replaced with an empty string.

SELECT REPLACE('Hello World' COLLATE Albanian_BIN ,'World','') as output

To get the list of available collations (case-sensitive and case-insensitive), use the following query.

SELECT Name, Description FROM fn_helpcollations()

Using SQL REPLACE Function with a Table and Column

The following example will replace an open parenthesis with a hyphen and a close parenthesis with a hyphen. So we nest 2 REPLACE calls together as follows.

SELECT REPLACE(REPLACE(PhoneNumber,'(','-'), ')','-') as replaced, PhoneNumber
FROM [Person].[PersonPhone]

Using SQL REPLACE Function to Replace Multiple Spaces with Single Space

In this example, we will replace multiple spaces with a single space. Again, we are using nested REPLACE statements. The code below converts spaces to square brackets and then back to a single space.

SELECT REPLACE(REPLACE(REPLACE('this     is an    example with   multiple      spaces',' ','[]'),'][',''),'[]',' ') as msg

Here is another way this could be written. This does 3 replaces like the statement above. It replaces 3 spaces with 1 space, 3 spaces with 1 space and then 2 spaces with 1 space, so that everything is single spaced.

SELECT REPLACE(REPLACE(REPLACE('this     is an    example with   multiple      spaces','   ',' '),'   ',' '),'  ',' ') as msg

We can use against a table as follows.

SELECT Description, REPLACE(REPLACE(REPLACE(Description,'   ',' '),'   ',' '),'  ',' ') as msg
FROM [Production].[ProductDescription]

Remove All Characters Except Numbers Using SQL REPLACE

The following example will keep the numbers and remove the characters. We will create a function to do that.

CREATE FUNCTION [dbo].[ufnGetNumbers](@string VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @string) > 0
    BEGIN
        SET @string = REPLACE(@string,SUBSTRING(@string,PATINDEX('%[^0-9]%',@string),1),'')
    END
    RETURN @string
END

Next, we use the function as follows.

SELECT [dbo].[ufnGetNumbers]('122SLFJLDkld;lfs34') as msg

The result will show only numbers.

Remove All Characters Except Letters Using SQL REPLACE

The following function will remove all characters except letters from a string.

CREATE FUNCTION [dbo].[ufnGetLetters](@string VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^Aa-Zz]%', @string) > 0
    BEGIN
        SET @string = REPLACE(@string,SUBSTRING(@string,PATINDEX('%[^Aa-Zz]%',@string),1),'')
    END
    RETURN @string
END

Next, we use the function as follows.

SELECT [dbo].[ufnGetLetters]('122S&*%LFJLDkld;lfs34') as msg

Additional Information

Resources

Community

Subscribe