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:
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:
Examples of REPLACE String function
Example 1: The following SELECT query replaces the character ‘R’ with ‘P’ in the orginal string:
Output:
| Website_Name |
|---|
| tutoraspire |
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 |
|---|
| tutoraspire |
Example 3: The following SELECT query replaces the substring ‘Tutorials’ with the new word ‘Articles’ in the given original string:
Output:
| tutoraspire_Sentence |
|---|
| tutoraspire 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:
2022tutoraspire2022
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:
The following CREATE statement creates the Student_Marks table:
The below INSERT queries insert the records of college Faculties in the Student_Marks table:
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:
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:
This SQL statement replaces the Chandigarh city and Punjab state of those students whose Student_ID is more than 4002.
Output:
| Student_Id | REPLACE(Student_City, ‘Chandigarh’, ‘Munnar’) | REPLACE(Student_State, ‘Punjab’, ‘Kerala ) |
|---|---|---|
| 4007 | Ghaziabad | Uttar Pradesh |
| 4004 | Jaipur | Rajasthan |
| 4011 | Munnar | Kerala |
| 4006 | Ghaziabad | Uttar Pradesh |
| 4010 | Lucknow | Uttar Pradesh |