REPLACE Function in SQL

This string function of Structured Query Language replaces all the occurrences of those characters in the original string with the given new character or substring. This function also replaces the value of the column with the new value.

Syntax of REPLACE String Function

Syntax 1: This syntax uses REPLACE function with the column name of the SQL table:

SELECT REPLACE(Column_Name, Character/string_to_replace, new_String/character ) AS Alias_Name FROM Table_Name;

In the syntax, we have to specify the name of that column whose values we want to replace.

Syntax 2: This syntax uses REPLACE function with string:

Syntax 3: This syntax uses REPLACE function with the individual character:

SELECT REPLACE(Original_String, character_to_Replace, New_Character) AS Alias_Name;

Examples of REPLACE String function

Example 1: The following SELECT query replaces the character 'R' with 'P' in the orginal string:

Output:

Website_Name
JAVATPOINT

Example 2: The following SELECT query replaces all the occurrences of character 'S' with the new character 'T' in the original string:

Output:

Website_Name
JAVATPOINT

Example 3: The following SELECT query replaces the substring 'Tutorials' with the new word 'Articles' in the given original string:

SELECT REPLACE(  'JavaTpoint provides various Tutorials.', 'Tutorials', 'Articles') AS JavaTpoint_Sentence;

Output:

JavaTpoint_Sentence
JAVATPOINT provides various Articles.

Example 4: The following SELECT query replaces the symbol from the original string with a new symbol:

Output:

Replace_Symbol
$$$$98221545$$

Example 5: The following SELECT query replaces the year in the original string:

Output:

2022JavaTpoint2022

Example 6: This example uses the REPLACE function with the table in Structured Query Language.

In this example, we have to create a new SQL table through which we will execute the REPLACE() function on columns. The syntax for creating the new table in the SQL database is as follows:

CREATE TABLE table_name
(
First_Column_of_table Data Type (character_size of 1st Column),  
Second_Column_of_table Data Type (character_size of the 2nd column ),  
Third_Column_of_table Data Type (character_size of the 3rd column),  
...  

Last_Column_of_table Data Type (character_size of the Nth column)
);  

The following CREATE statement creates the Student_Marks table:

CREATE TABLE Student_Marks
(
Student_ID INT NOT NULL PRIMARY KEY,  
Student_First_Name VARCHAR (100),  
Student_Middle_Name VARCHAR (100),  
Student_Last_Name VARCHAR (100), 
Student_Class INT NOT NULL,
Student_City Varchar(120),
Student_State Varchar (80),
Student_Marks INT 
);

The below INSERT queries insert the records of college Faculties in the Student_Marks table:

INSERT INTO Student_Marks (Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) VALUES (4001, Aman, Roy, Sharma, 4, Chandigarh, Punjab, 88);

INSERT INTO Student_Marks 
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) 
VALUES ( 4002, Vishal, Gurr, Sharma, 8, Murthal, Haryana, 95 );

INSERT INTO Student_Marks 
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) 
VALUES (4007, Raj, singhania, Gupta, 6, Ghaziabad, Uttar Pradesh, 91);

INSERT INTO Student_Marks 
(Student_ID, Student_First_Name, Student_Middle_Name Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)  
VALUES (4004, Yash, Chopra, Singhania, 9, Jaipur, Rajasthan, 85);

INSERT INTO Student_Marks 
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) 
VALUES (4011, Vinay, Sharma, Roy, 8, Chandigarh, Punjab, 94);

INSERT INTO Student_Marks 
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) 
VALUES (4006, Manoj, singhania, Gupta, 5, Ghaziabad, Uttar Pradesh, 83);


INSERT INTO Student_Marks 
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) 
VALUES (4010, Ram, Raheem, Gupta, 9, Lucknow, Uttar Pradesh, 89);

The following SELECT statement displays the inserted records of the above Student_Marks table:

Student_Id	Student_First_Name	Student_Middle_Name	Student_Last_Name	Student_Class	Student_City	Student_State	Student_Marks
4001	Aman	Roy	Sharma	4	Chandigarh	Punjab	88
4002	Vishal	Gurr	Sharma	8	Murthal	Haryana	95
4007	Raj	singhania	Gupta	6	Ghaziabad	Uttar Pradesh	91
4004	Yash	Chopra	Singhania	9	Jaipur	Rajasthan	85
4011	Vinay	Sharma	Roy	8	Chandigarh	Punjab	94
4006	Manoj	Singhania	Gupta	5	Ghaziabad	Uttar Pradesh	83
4010	Ram	Raheem	Gupta	9	Lucknow	Uttar Pradesh	89

Query 1: The following SELECT query uses REPLACE function with the Student_Last_Name column of the above Student_Marks table:

SELECT Student_Last_Name, REPLACE(Student_Last_Name, 'a', 'r') AS REPLACE_a_r FROM Student_Marks;

This SQL statement replaces all the occurrences of 'a' with 'r' in the last name of each student:

Output:

Student_Last_Name	Replace_a_r
Sharma	Shrrmr
Sharma	Shrrmr
Gupta	Guptr
Singhania	Singhrnir
Roy	Roy
Gupta	Guptr
Gupta	Guptr

Query 2: The following SELECT query uses REPLACE function with the Student_City and Student_State column of those students whose Student_Id is greater than 4002 in the above Student_Marks table:

SELECT Student_Id, REPLACE(Student_City, 'Chandigarh', 'Munnar'), REPLACE(Student_State,  'Punjab', 'Kerala ) FROM Student_Marks WHERE Student_Id >4002;

This SQL statement replaces the Chandigarh city and Punjab state of those students whose Student_ID is more than 4002.

Output: