This article explains the SQL REPLACE Function and explores how to use this function to change or update the existing table column’s text with a new string. Let’s start this page by explaining the simple definition, syntax, and basic example, replacing table columns, replacing Nulls with 0, and updating old text with the new string.
The SQL REPLACE function is a powerful string manipulation method that helps replace all occurrences of an existing old substring with a user-specified new string. While working with database tables, we encounter wrongly typed words or inaccurate product descriptions. This function allows you to manipulate that data. It finds the wrong text (whether a substring, word, or character) and replaces that part with new data.
Due to its quick text replacement, it is particularly beneficial for tasks such as data cleaning, text manipulation, and transformations. Some real-time scenarios where the SQL Server Replace Function comes into play are:
- Correcting the typo errors.
- Replacing the “( )” in the Phone Number.
- Replacing NULLS with zeros.
- Removing extra spaces, special characters, or symbols from text.
- Updating misspelled city names.
- Making data consistent.
- Updating outdated content with more relevant one.
- Inserting New URLS in the place of old URLs.
In all these scenarios, it is impractical to change one by one manually. So, it would be best to use the SQL Server REPLACE Function to find the substring and replace it with a new string.
NOTE: The old string (Arguments 2) is case-insensitive. It treats A and a as the same entries, so you don’t have to worry about typing the exact matching one.
SQL Server REPLACE Function Syntax
The syntax of the Replace string function is as follows:
SELECT REPLACE (Expression, string_pattern, Replacing_Text) FROM [Source]
Parameters
- Expression: String to perform a search. This function will substitute a portion or word present in this Expression.
- string_pattern: Text you want to change, and it can be a character, word, or substring. Anything you put here, this SQL Function will replace all occurrences of it with a new one. If you pass an empty string as the parameter value, the output returns unchanged text.
- Replacing_Text: New sentence you want to embed into Expression. If you pass an empty string as the Replacing_Text, the output will remove all occurrences of the string_pattern.
Return Value
In general, it returns VARCHAR output. But if one of the input values is NVARCHAR, the SQL REPLACE Function will return NVARCHAR output. Anything above them will automatically truncate. If any argument is NULL, it returns NULL as an output. For instance, the query below uses the Country column and replaces the US with the United States of America.
SELECT REPLACE(Country, ‘US’, ‘The United States of America’)
FROM countries;
The REPLACE function is your solution if you need to change specific text within a column, update values, replace numbers in string columns, or replace NULL values.
SQL REPLACE Function in SELECT Statement
This String Function changes the original text with the specified new one. Generally, the REPLACE function can be used in the SELECT statement or UPDATE statement to perform text replacement or manipulation.
The following section shows multiple ways to use the String REPLACE function. We start the section by working on direct input and variables, and then move to a database table.
Replacing a Single Character
As we mentioned earlier, you can use this function to find and replace a single character. This query will find every occurrence of t in the tutorial Gateway and replace it with A.
SELECT REPLACE('tutorial Gateway', 't', 'A') result
AuAorial GaAewayNOTE: Before performing a word or character replacement, always back up the table. Whenever possible, use the TRANSACTIONS to avoid unnecessary issues.
Replacing a Word or Substring in a string
In this example, the SQL REPLACE() function looks for the ABC word within the ‘Welcome to ABC Corporation. Enjoy ABC Workspace!’ string. Next, it replaces each Occurrence of ABC with Amitabh Bachchan.
SELECT
REPLACE(
'Welcome to ABC Corporation. Enjoy ABC Workspace!',
'ABC',
'Amitabh Bachchan'
) result;
Welcome to Amitabh Bachchan Corporation. Enjoy Amitabh Bachchan Workspace!Working on Variables
In this sample query, we declare a variable and assign the data. First, we will substitute Anything with Everything from the variable @Expression. Next, we used it directly on the ‘Welcome to Hello World’ text to change Hello to New.
DECLARE @Expression varchar(50)
SET @Expression = 'Learn Anything From Here'
SELECT REPLACE (@Expression, 'Anything', 'Everything') AS 'Result'
SELECT REPLACE ('Welcome to Hello World', 'Hello', 'New') AS 'Result'
OUTPUT
Result
----------------------------
Learn Everything From Here
(1 row affected)
Result
----------------------------
Welcome to New WorldReplacing Numbers
Apart from the text information, you can use this REPLACE function to replace the digits or part of the existing numbers. The two statements below will update digit 2 with 1.
SELECT REPLACE('2025', '2', '1') AS Output1;
SELECT REPLACE(2025, 2, 1) AS Output2;
Output1
--------
1015
Output2
--------
1015SQL REPLACE Function in Table Columns
In all the above examples, we used the variables as the arguments for both the expression, old, and new. Here, we will use the REPLACE function on table columns and use the SELECT statement to show the changes.
The query below replaces the part of a Title column text instead of the whole column. Here, it looks for the Production substring, and if the REPLACE function finds it, the text updates with Site. Next, the ALIAS keyword assigns a name to it as NewTitle.
SELECT [SNo]
,[Name]
,[EmailAddress]
,[Title]
,REPLACE(Title, 'Production', 'Site') AS NewTitle
,[DepartmentName]
FROM [EmpDetails]
Working with Email Address
In the two examples below, we use a SELECT statement with an extra column to showcase the working functionality of the SQL REPLACE() Function on Email address columns.
It also allows you to replace the part of a string, column values, or text beside the variables. In this String Function example, we will substitute the .com with .org from the Email column in SQL Server.
SELECT *
,REPLACE ([EmailAddress], 'com','org' ) AS [Output1]
FROM [EmpDetails]
In this example, we will substitute the @ symbol in the Email Address column with a space.
SELECT *
,REPLACE ([EmailAddress], '@',' ' ) AS [Output2]
FROM [EmpDetails]
Remove a Word from string
Using the SQL REPLACE() function with an empty string as the second argument will remove a word or substring from the given text. Here, we completely removed the Technician word from all the rows whose title has Technician in it.
SELECT *
,REPLACE(Title, 'Technician', '') AS SubTitle
FROM [EmpDetails]
SQL Nested REPLACE function – Working Phone Numbers
While working with data reporting or data transformation, Phone numbers are the major roadblocks. Each country or store saves the numbers in a particular format, and they differ from one another when you combine data from multiple countries. To remove the extra spaces, special characters, + symbols, (), etc, you can rely on the REPLACE function.
DECLARE @P1 VARCHAR(100) = '(111) 555 500 1212';
DECLARE @P2 VARCHAR(100) = '1 (11) 500 555-0162';
DECLARE @P3 VARCHAR(100) = '212-555-0187';
DECLARE @P4 VARCHAR(100) = '+919123456789';
SELECT REPLACE(REPLACE(@P1, '(', ''), ')', '') AS P1
,REPLACE(REPLACE(REPLACE(@P2, '(', ''), ')', ''), '-', '') AS P2
,REPLACE(@P3, '-', ' ') AS P3
,REPLACE(@P3, '-', '') AS P4
,REPLACE(@P4, '+91', '') AS P5
P1 P2 P3 P4 P5
111 555 500 1212 1 11 500 5550162 212 555 0187 2125550187 9123456789TIP: Always limit the number of nested REPLACE function usages to avoid confusion and improve the query performance.
Replacing Multiple Spaces with a single space
In most cases, a text column may come with a small number of records where words are separated by multiple spaces (not a single one). While performing data cleaning, we must replace extra spaces with a single space, and to deal with the situation, this function comes in handy.
DECLARE @Text VARCHAR(1000) = 'We provide free tutorials for you.';
WHILE CHARINDEX(' ', @Text) > 0
BEGIN
SET @Text = REPLACE(@Text, ' ', ' ');
END
SELECT @Text AS New;
New
----------------------------
We provide free tutorials for you.SQL REPLACE Function in UPDATE Statement
The above-shown example illustrates how this function returns the output using the SELECT statement. However, the most common use of the REPLACE function is within an UPDATE statement to manipulate a specific string within a column. For example, changing a particular category’s old product description with new and engaging content.
In this example, we used the REPLACE() function to update the Email column from the Employee table. The query below checks all rows to find yahoo.com. It replaces every occurrence with tutorialgateway.org. Here, the UPDATE statement will change the column value in real time, so be careful.
UPDATE [EmpDetails]
SET [EmailAddress] = REPLACE([EmailAddress],'yahoo.com','tutorialgateway.org');
SELECT * FROM [EmpDetails]
Using the WHERE clause
Suppose you use the REPLACE() Function with the combination of the UPDATE statement and WHERE clause. In that case, you can restrict the string replacement to a single column value or a few rows that satisfy the condition.
In this example, the SQL REPLACE() function updates the Email column from adventure-works.com to hotmail.com of an employee whose Department name is Engineering. It first filters the employees who belong to engineering. Although there are two employees in the Engineering department, it replaces a single value because the other one has a tutorialgateway.org email address.
UPDATE [EmpDetails]
SET [EmailAddress] = REPLACE([EmailAddress], 'adventure-works.com, 'hotmail.com')
WHERE DepartmentName = 'Engineering';
SELECT * FROM [EmpDetails];
Updating a Part of a String
In the above two examples, we updated the whole string in a column with completely new text. However, you can use the SQL REPLACE function to update a part of a whole string. For example, you can change all the employees’ Email domain names from abc.com to tutorialgateway.org or replace domain extensions, etc. The query below replaces the employee’s email domain extensions from com to org.
UPDATE [EmpDetails]
SET [EmailAddress] = REPLACE([EmailAddress], 'com', 'org');
SELECT * FROM [EmpDetails];
SQL REPLACE NULL with 0
In real-time, NULL values are the biggest headache. While working with a database, you will encounter NULLS; you can use this function to deal with them. For instance, this Function replaces NULL with 0, a default value, or default text.
The example below replaces the NULLS in the Employee table Department Name column with Sales. Here, the ISNULL function finds and changes NULLs with a NULL value. Next, the REPLACE() function updates the NULL with Sales. You can also use ISNULL(DepartmentName, ‘Sales’) without the REPLACE function.
SELECT *
,REPLACE(ISNULL(DepartmentName, 'NULL'), 'NULL', 'Sales') AS NewDept FROM [EmpDetails]
The other most useful option is to use the COALESCE function to identify the NULL values and the REPLACE function to update them with Sales.
SELECT *
,REPLACE(COALESCE(DepartmentName, 'NULL'), 'NULL', 'Sales') AS Dept
FROM [EmpDetails]
SQL REPLACE Function With regex
Until now, we explained the REPLACE() function to change existing text with new. However, the real potential is using regular expressions to find the string to replace, which allows you to use regex.
Remove Special Characters from a String
There are situations where people mistype special characters within the description or any other column. There are multiple ways to remove those special characters, such as @, #, $, %, etc, from text.
To demonstrate this, we declared a sample text that contains numbers, letters, and special characters. Next, we used PATINDEX() to find the alphabet (upper and lowercase characters) and numbers.
DECLARE @str NVARCHAR(MAX) = 'Hell!!o..,() @World!*123#';
WHILE PATINDEX('%[^a-zA-Z0-9]%', @str) > 0
SET @str = STUFF(@str, PATINDEX('%[^a-zA-Z0-9]%', @str), 1, '');
SELECT @str AS Cleaned;
Cleaned
------------
HelloWorld123Removing Numbers from a String
The query below removes the numeric digits from the customer’s Email ID. Here, the PATINDEX() function finds the position of the numeric values or digits from 0 to 9 within the EmailAddress column.
The SUBSTRING function reads the first two characters from that position because every email address has two digits. Then the SQL REPLACE() function replaces those two digits with the empty string. If the email has a single digit, change the query from 2 to 1.
SELECT [CustomerKey],[FirstName],[LastName],[EmailAddress],
REPLACE([EmailAddress],
SUBSTRING([EmailAddress],
PATINDEX('%[0-9]%', [EmailAddress] COLLATE Latin1_General_BIN),
2),'') AS Result
FROM [DimCustomer]
Although we used the DimCustomer example, the image shows the EmpDetails output. However, copy and paste the code above to see the changes in the AdventureWorksDW database, if you need the code for the image below.
SELECT *
,REPLACE(TRANSLATE([EmailAddress], '0123456789', ' '), ' ', '') AS Email
FROM [EmpDetails]
The other option is using the while loop.
DECLARE @str VARCHAR(1000) = 'savannah39doe40@adventure-works123.com';
DECLARE @Result VARCHAR(1000) = '';
WHILE PATINDEX('%[0-9]%', @str) > 0
BEGIN
-- Replace the first number with an empty string
SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[0-9]%', @str), 1), '');
END
SET @Result = @str;
SELECT @Result AS OnlyText;
OnlyText
----------------------------
savannahdoe@adventure-works.comDelete Alphabets or all characters except Numbers
This query deletes all the characters that include upper and lower case alphabets and special characters except numbers from 0 to 9.
DECLARE @Email VARCHAR(1000) = 'savannah39doe40@adventure-works123.com';
DECLARE @out VARCHAR(1000) = '';
WHILE PATINDEX('%[0-9]%', @Email) > 0
BEGIN
SET @out = @out + SUBSTRING(@Email, PATINDEX('%[0-9]%', @Email), 1);
SET @Email = REPLACE(@Email, SUBSTRING(@Email, PATINDEX('%[0-9]%', @Email), 1), '');
END
SELECT @out AS Numbers;
Numbers
----------------------------
394012
Comments are closed.