SQL Server REPLACE Function


By:

The REPLACE function is used to replace a string or substring of a string with another string.

Syntax

REPLACE(expression, stringToReplace, stringReplacement)

Parameters

  • expression - this is the character string or expression to be replaced completely or partially, it can be varchar, nvarchar or binary.
  • stringToReplace – this is the string to be replaced, it can be varchar, nvarchar or binary.
  • stringReplacement – this is the string replacement, it can be varchar, nvarchar or binary.

Simple REPLACE Example

The following example will replace the word "World" with "MSSQLTIPS".

SELECT REPLACE('Hello World','World','MSSQLTIPS') as output
T-SQL replace simple example

NULL Values in 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
REPLACE work with null values

Remove a Word with REPLACE function

The following example will remove the word "World" from "Hello World".

SELECT REPLACE('Hello World','World','') as output
Replace word with empy string in T-SQL

Use 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 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
replace with collation

To get the list of available collations, use the following query.

SELECT Name, Description FROM fn_helpcollations() 

Using 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]
replace query results

Using 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
replace query results

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 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.

replace query results

Remove All Characters Except Letters Using 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
replace query results

Related Articles


Last Update: 11/10/2021




Comments For This Article





download














get free sql tips
agree to terms