1. Introduction
SQL defines the REPLACE function which takes three required arguments and returns a new string in which all occurrences of the substring are replaced by a new substring.
REPLACE(input_string_arg, searching_substring_arg, replacing_substring_arg)
- input_string_arg – required, the searching source data.
- searching_substring_arg – required, the substring to be replaced.
- replacing_substring_arg – required, the replacement substring to be used to replace the searching_substring_arg.
Note: it returns a null if any of the arguments is a null.
2. Technologies Used
The example code in this article was built and run using:
- MySQL
- SQL
3. Setup Database
In this step, I will use mysqlDemo database to show the SQL Replace function. Click here on how to set up.
show databases
mysql> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysqlDemo | | performance_schema | | sys | +--------------------+ 5 rows in set (0.04 sec) use mysqlDemo;
4. Demo Replace Function
In this step, I will show several ways to use the Replace function in a Select statement.
4.1 Replace in Select Statement
The following select statement replaces the word apple with orange in the “I like apple, he likes apple” sentence. The result string became “I like orange, he likes orange”.
Select with Replace Function
mysql> SELECT
-> REPLACE(
-> 'I like apple, he likes apple',
-> 'apple',
-> 'orange'
-> ) newString;
+--------------------------------+
| newString |
+--------------------------------+
| I like orange, he likes orange |
+--------------------------------+
1 row in set (0.00 sec)4.2 Replace in Select Statement with Column
There are four rows in the demo_table as the following:
Select * from demo_table
mysql> select * from demo_table; +----+-----------+-------+------+ | id | name | phone | type | +----+-----------+-------+------+ | 1 | mary | NULL | 1 | | 2 | shan | NULL | 2 | | 3 | zheng | NULL | 3 | | 4 | mary test | NULL | 4 | +----+-----------+-------+------+ 4 rows in set (0.01 sec)
In this step, I will use the replace function to transform the name column value by replacing every “mary” substring with the “JCG” substring.
SELECT name, REPLACE(name, “mary”, “JCG”) replacedName from demo_table
mysql> SELECT name, REPLACE(name, "mary", "JCG") replacedName from demo_table; +-----------+--------------+ | name | replacedName | +-----------+--------------+ | mary | JCG | | shan | shan | | zheng | zheng | | mary test | JCG test | +-----------+--------------+ 4 rows in set (0.00 sec)
4.3 Replace in Update Statement
We can use the REPLACE function in the UPDATE statement to correct the value. In this step, I will update the name column in the demo_table by replacing “mary” with “JCG“.
update demo_table
set name = replace(name,’mary’, ‘JCG’)
where id < 10
mysql> update demo_table
-> set name = replace(name,'mary', 'JCG')
-> where id <10;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 4 Changed: 2 Warnings: 0
mysql> select * from demo_table;
+----+----------+-------+------+
| id | name | phone | type |
+----+----------+-------+------+
| 1 | JCG | NULL | 1 |
| 2 | shan | NULL | 2 |
| 3 | zheng | NULL | 3 |
| 4 | JCG test | NULL | 4 |
+----+----------+-------+------+
4 rows in set (0.00 sec)
mysql>As you can see here, two records are updated. Both “mary” and “mary test” become “JCG” and “JCG test“.
5. Summary
The SQL REPLACE function is a common utility function which can be used anywhere accepting a String value. In this example, I demonstrated how to use it in both SELECT and UPDATE statements.
6. Download the Source Code
You can download the full source code of this example here: SQL Server REPLACE() Function
