MySQL表中的字符串替换

最新推荐文章于 2024-06-29 18:32:19 发布

lazying_bird

最新推荐文章于 2024-06-29 18:32:19 发布

阅读量1.2k

点赞数

分类专栏： SQL 文章标签： mysql replace

本文链接：https://blog.csdn.net/a_flying_bird/article/details/51727708

版权

SQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

对于表中某个字段中的内容进行字符串替换。

语法：
UPDATE table_name SET field_name=replace(field_name, from_str, to_str);

示例：

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> describe test;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name        | varchar(20)  | NO   |     | NULL    |       |
| age         | int(11)      | NO   |     | NULL    |       |
| description | varchar(100) | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [test]> select * from test;
+-------+-----+------------------------------+
| name  | age | description                  |
+-------+-----+------------------------------+
| Tom   |   5 | some string ABCDEFG is here. |
| Jerry |   4 | ????????????ABCDEFG?         |
| John  |   5 | ????ABCDEFG???               |
| Tom2  |   5 | f                            |
| Tom3  |   5 | f????abce                    |
+-------+-----+------------------------------+
5 rows in set (0.00 sec)

MariaDB [test]> update test set description=replace(description, "ABCDEFG", "abcdefg");
Query OK, 3 rows affected (0.03 sec)
Rows matched: 5  Changed: 3  Warnings: 0

MariaDB [test]> select * from test;
+-------+-----+------------------------------+
| name  | age | description                  |
+-------+-----+------------------------------+
| Tom   |   5 | some string abcdefg is here. |
| Jerry |   4 | ????????????abcdefg?         |
| John  |   5 | ????abcdefg???               |
| Tom2  |   5 | f                            |
| Tom3  |   5 | f????abce                    |
+-------+-----+------------------------------+
5 rows in set (0.00 sec)

MariaDB [test]>