By: Daniel Calbimonte
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.
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
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
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
Related SQL Commands and Tutorials
- SQL Server REPLACE
- Using the SQL ISNULL() Function
- SQL Server Collation Overview and Examples
- Deciding between COALESCE and ISNULL in SQL Server
- The Many Uses of Coalesce in SQL Server
- How to Use SQL Server Coalesce to Work with NULL Values
- CONCAT and CONCAT_WS function in SQL Server
- Multiple Ways to Concatenate Values Together in SQL Server
- Getting Started with SQL INNER JOIN
- SQL INNER JOIN, LEFT JOIN and RIGHT JOINExamples
- SQL String Functions Tutorial