REPLACE function in SQL

The REPLACE function in SQL is used to replace occurrences of a specified substring within a string with another substring.

SQL Syntax

REPLACE(string, substring, replacement)

The function has the following parameters:

string: The original string where the replacement will take place.
substring: The substring that you want to replace.
replacement: The string that will replace the substring.

The REPLACE function searches the string for all occurrences of the substring and replaces them with the replacement. If the substring is not found within the string, the REPLACE function returns the original string unchanged.

Example: Create the Employees table.

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Age int
);

Insert the data in the Employees table.

SQL

insert into Employees values
(1, "Ashish", "Goel", 25), 
(2, "Kirshan", "Gupta", 30),
(3, "Anjali", "Sharma", 25),
(4, "Manju", "Saini", 15),
(5, "Katrina", "Kaif", 18),
(6, "Esha", "Dixit", 20),
(7, "Ankita", "Verma", 19), 
(8, "Meenakshi", "Chikkara", 35),
(10, "Alia", "Bhatt", 16);

By using the Create table and insert statement we reach to the following table in our database:

Table: Employees

EmployeeID	First Name	Last Name	Age
1	Ashish	Goel	25
2	Kirshan	Gupta	30
3	Anjali	Sharma	25
4	Manju	Saini	15
5	Katrina	Kaif	18
6	Esha	Dixit	20
7	Ankita	Verma	19
8	Meenakshi	Chikkara	35
10	Alia	Bhatt	16

SQL

SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    Replace(firstname, "Ashish" , "Aakanksha") as AfterReplacement
FROM Employees;

The output of the above query is shown below:

EmployeeID	FirstName	LastName	AfterReplacement
1	Ashish	Goel	Aakanksha
2	Kirshan	Gupta	Kirshan
3	Anjali	Sharma	Anjali
4	Manju	Saini	Manju
5	Katrina	Kaif	Katrina
6	Esha	Dixit	Esha
7	Ankita	Verma	Ankita
8	Meenakshi	Chikkara	Meenakshi
10	Alia	Bhatt	Alia

Previous Next