SQL - REPLACE() Function

---

---

The SQL REPLACE() function is used to replace a substring from a String. This method accepts three string values as parameters and replaces all the occurrences of a particular part of a string (substring) with another one. If any of the argument passed to this function is NULL, it returns NULL value in the result.

Note − The REPLACE() function does not perform a case-sensitive match when replacing the substring with the specified string.

Syntax

Following is the syntax of SQL REPLACE() function −

REPLACE(str,from_str,to_str)

Parameters

• str − Original string.
• from_str − Substring you want to replace.
• to_str − Substring with which you need to replace.

Example

In the following example,we are using the REPLACE() function to replace all the ‘Hello’ with ‘Hi’ from the string ‘Hello World’;

SELECT REPLACE('Hello World', 'Hello', 'Hi');

Output

Following is the output of the above query −

+---------------------------------------+
| REPLACE('Hello World', 'Hello', 'Hi') |
+---------------------------------------+
| Hi World                              |
+---------------------------------------+

Example

You can also pass numerical values to this function.

In the following example, we are passing a numeric value '123012' to REPLACE() function and replacing the '3012' with '4123'.

SELECT REPLACE(123012,3012,4123);

Output

Following is the output of the above query −

+---------------------------+
| REPLACE(123012,3012,4123) |
+---------------------------+
| 124123                    |
+---------------------------+

Example

While replacing the substring, this function does not perform a case-sensitive match. If you try to replace the substring by changing the case of the specified string, the query will execute without any error.

SELECT REPLACE('Structured query Language', 'QUERY', 'Query');

Output

Following is the output of the above query −

+--------------------------------------------------------+
| REPLACE('Structured query Language', 'QUERY', 'Query') |
+--------------------------------------------------------+
| Structured Query Language                              |
+--------------------------------------------------------+

Example

You can also pass column name of a table as an argument to this function and replace the part of the values in it. Assume we have created a table in SQL table with name Customers as Shown below −

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4)
 );

Now let's insert seven records into the customers table using the INSERT statement as follows:−

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Khilan', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Chaitali', 25, 'Mumbai', 6500.00 );

Following SQL query replaces the string 'Ramesh' in the entities of the column, "Name" with 'Dinesh' using the REPLACE() function −

SELECT ID, NAME, REPLACE(NAME, 'Ramesh', 'Dinesh') from CUSTOMERS;

Output

Following is the output of the above query −

+----+----------+-----------------------------------+
| ID | NAME     | REPLACE(NAME, 'Ramesh', 'Dinesh') |
+----+----------+-----------------------------------+
|  1 | Ramesh   | Dinesh                            |
|  2 | Khilan   | Khilan                            |
|  3 | kaushik  | kaushik                           |
|  4 | Chaitali | Chaitali                          |
+----+----------+-----------------------------------+