sql

SQL Server REPLACE() Function

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

Download
You can download the full source code of this example here: SQL Server REPLACE() Function

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button