REPLACE SQL Function | Replace Data Within String use REPLACE SQL

sql replace function
Summary: in this tutorial, you will learn how to use them SQL REPLACE() function to find and replace data within strings, where you look for a substring within a string and then replace it. furthermore, I will demonstrate how to use the function SQL SERVER REPLACE with an example.

REPLACE SQL function overview

The SQL REPLACE() function is used to replace all occurrences of a substring within a string with a new substring as follows:

REPLACE(input_string, substring, new_substring);

1	REPLACE(input_string, substring, new_substring);

In this syntax:
input_string: The input string value on which the SQL replace function has to operate.
substring: The substring to evaluate and provides a reference position to the replacement field.
new_substring: REPLACEs the specified string or character value of the given expression.

SQL REPLACE() Function Applies To:

Therefore, these function can be used in the following versions of SQL Server (Transact-SQL):
SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Important Note:

The search is case-insensitive.
The SQL SERVER REPLACE function returns a new string in which all occurrences of the substring.
are replaced by the new_substring. furthermore, It returns NULL if any argument is NULL.
The REPLACE SQL SERVER function performs comparisons based on the collation of the input expression.

I) Using REPLACE() Function in SQL with Literal strings

There is the following example given below uses the REPLACE() SQL function to replace me with the You in the string ‘Tell me and I forget. Teach me and I saw’:

SQL REPLACE Statement is:

SELECT 
    REPLACE(
        'Tell me and I forget. Teach me and I saw', 
        'me', 
        'you'
    );

SELECT

REPLACE(

'Tell me and I forget. Teach me and I saw',

'me',

'you'

);

Tell you and I forget. Teach you and I saw.
(1 row affected)

1 2	Tell you and I forget. Teach you and I saw. (1 row affected)

therefore, you can see from the output, all occurrences of me were replaced with you.

II) Using SQL REPLACE() function with table columns

In this example, we will use the sample table Employees_Details as given below.

EmpID	Name	Phone	City	State	PinCode
111	James Hudson	(845) 705-7576	France	Paris	226015
112	Thomas sawyer	(745) 505-1176	Buenos Aires	Argentina	226016
113	Ryan parker	(861) 103-7171	Gabon	Libreville	225678
114	Joshua Jameson	(215) 305-3536	México D.F.	México	226012
115	John Greyson	(441) 447-5596	Greece	Athens	236056
116	Joshua Jameson	(635) 973-7171	Guyana	Georgetown	236011
117	Daniel Jeremiah	(955) 999-2236	Mannheim	Germany	442317
118	Michael Nolan	(881) 799-4312	Honduras	Tegucigalpa	236017
119	Sebastian Colton	(155) 401-4006	London	UK	236014
120	Owen Dominic	(144) 665-9446	Strasbourg	France	446778
121	Elijah Connor	(220) 195-1116	London	UK	236014
122	Benjamin Hunter	(111) 177-9006	Italy	Rome	442315
123	Logan Aaron	(333) 005-1001	Mannheim	Germany	442317
124	Nathan Miles	(777) 139-7776	Strasbourg	France	446778
125	Caleb Everett	(233) 335-6119	Liberia	Monrovia	459867

SQL REPLACE Statement is:

SELECT 
    EmpID,   
	Name,
	City, 
	Phone, 
	REPLACE(REPLACE(Phone, '(', ''), ')', '') Phone_modified
FROM    
	Employees_Details
WHERE Phone IS NOT NULL
ORDER BY 
	Name;

SELECT

EmpID,

Name,

City,

Phone,

REPLACE(REPLACE(Phone, '(', ''), ')', '') Phone_modified

FROM

Employees_Details

WHERE Phone IS NOT NULL

ORDER BY

Name;

let’s understand, the above query How its work
The first call REPLACE(Phone, '(', '') replaces the character ‘(‘ in the phone number by a space e.g., (716) 112-6112 -> 716) 112-6112
The second call reuses the result of the first call and replaces the character ‘)’ by a space e.g., 716) 112-6112 -> 716 112-6112.

III) Using SQL REPLACE() function to Correct Data in Tables

The REPLACE() function is often used to correct data in a table. For example, replacing the outdated link or records with the new one.

There is the following SQL Syntax:

UPDATE 
    table_name
SET
    column_name = REPLACE(column_name, 'old_string','new_string')
WHERE
    condition;

UPDATE

table_name

SET

column_name = REPLACE(column_name, 'old_string','new_string')

WHERE

condition;

Let’s suppose for example, in the above given following table we want to change the city code of the phone numbers from 215 to 445, you use the following SQL statement:

UPDATE
    Employees_Details
SET
    Phone = REPLACE(Phone,'(215)','(445)')
WHERE
    Phone IS NOT NULL;

UPDATE

Employees_Details

SET

Phone = REPLACE(Phone,'(215)','(445)')

WHERE

Phone IS NOT NULL;

Note:
You should take the back up the table before performing replacements or UPDATE table.

Conclusion:

In this tutorial, you have learned how to use the SQL SERVER REPLACE function to search and replace all occurrences of a substring with a new string with an example. I hope you will enjoy it! Thanks :)-

REPLACE SQL Function | Replace Data Within String use REPLACE SQL

REPLACE SQL function overview

SQL REPLACE() Function Applies To:

Important Note:

I) Using REPLACE() Function in SQL with Literal strings

SQL REPLACE Statement is:

II) Using SQL REPLACE() function with table columns

SQL REPLACE Statement is:

III) Using SQL REPLACE() function to Correct Data in Tables

There is the following SQL Syntax:

Conclusion:

Related Posts

About Author

admin