今天,有网友邮件问我,怎么样过滤掉字符串中的数字,比如:
mysql> select * from TEST;
+---------+------------------------+
| Contact | Address |
+---------+------------------------+
| A | 3995 Thomas Drive |
| B | 95 Dewberry Ct |
| C | 635 mill st |
| D | 3050 E 6TH ST |
| E | 3216 Lawndale Ave |
| F | 207 240th st. |
| G | 19 Lockhouse Rd. #14-3 |
+---------+------------------------+
希望能将address中的数字全部过滤掉,变成如下格式:
+---------+---------------------+
| contact | FILTER_NUM(ADDRESS) |
+---------+---------------------+
| A | Thomas Drive |
| B | Dewberry Ct |
| C | mill st |
| D | E TH ST |
| E | Lawndale Ave |
| F | th st. |
| G | Lockhouse Rd. #- |
+---------+---------------------+
马上想到使用mysql中字符串的replace功能来实现,但是查帮助后发现,replace只能对某个字符串进行替换,而无法对多个字符串替换,于是想到了一个笨办法,循环使用replace,如下:
mysql> select ADDRESS,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ADDRESS,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') filter_num from TEST;
+------------------------+-------------------+
| ADDRESS | filter_num |
+------------------------+-------------------+
| 3995 Thomas Drive | Thomas Drive |
| 95 Dewberry Ct | Dewberry Ct |
| 635 mill st | mill st |
| 3050 E 6TH ST | E TH ST |
| 3216 Lawndale Ave | Lawndale Ave |
| 207 240th st. | th st. |
| 19 Lockhouse Rd. #14-3 | Lockhouse Rd. #- |
+------------------------+-------------------+
7 rows in set (0.00 sec)
当然,也可以写一个函数,以后遇到类似问题也可以使用:
mysql> delimiter //
mysql> create function filter_num(str varchar(100)) returns varchar(100)
-> reads sql data
-> return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select *,FILTER_NUM(ADDRESS) from TEST;
+---------+------------------------+---------------------+
| Contact | Address | FILTER_NUM(ADDRESS) |
+---------+------------------------+---------------------+
| A | 3995 Thomas Drive | Thomas Drive |
| B | 95 Dewberry Ct | Dewberry Ct |
| C | 635 mill st | mill st |
| D | 3050 E 6TH ST | E TH ST |
| E | 3216 Lawndale Ave | Lawndale Ave |
| F | 207 240th st. | th st. |
| G | 19 Lockhouse Rd. #14-3 | Lockhouse Rd. #- |
+---------+------------------------+---------------------+
7 rows in set (0.00 sec)
不过,这种方法效率不高,因为要对文本进行10次替换,如果文本很大,效率将非常低下,如果要更加高效,可以写更复杂的函数,来进行一次扫描,就完成全部字符串的替换。
mysql> select * from TEST;
+---------+------------------------+
| Contact | Address |
+---------+------------------------+
| A | 3995 Thomas Drive |
| B | 95 Dewberry Ct |
| C | 635 mill st |
| D | 3050 E 6TH ST |
| E | 3216 Lawndale Ave |
| F | 207 240th st. |
| G | 19 Lockhouse Rd. #14-3 |
+---------+------------------------+
希望能将address中的数字全部过滤掉,变成如下格式:
+---------+---------------------+
| contact | FILTER_NUM(ADDRESS) |
+---------+---------------------+
| A | Thomas Drive |
| B | Dewberry Ct |
| C | mill st |
| D | E TH ST |
| E | Lawndale Ave |
| F | th st. |
| G | Lockhouse Rd. #- |
+---------+---------------------+
马上想到使用mysql中字符串的replace功能来实现,但是查帮助后发现,replace只能对某个字符串进行替换,而无法对多个字符串替换,于是想到了一个笨办法,循环使用replace,如下:
mysql> select ADDRESS,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ADDRESS,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') filter_num from TEST;
+------------------------+-------------------+
| ADDRESS | filter_num |
+------------------------+-------------------+
| 3995 Thomas Drive | Thomas Drive |
| 95 Dewberry Ct | Dewberry Ct |
| 635 mill st | mill st |
| 3050 E 6TH ST | E TH ST |
| 3216 Lawndale Ave | Lawndale Ave |
| 207 240th st. | th st. |
| 19 Lockhouse Rd. #14-3 | Lockhouse Rd. #- |
+------------------------+-------------------+
7 rows in set (0.00 sec)
当然,也可以写一个函数,以后遇到类似问题也可以使用:
mysql> delimiter //
mysql> create function filter_num(str varchar(100)) returns varchar(100)
-> reads sql data
-> return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select *,FILTER_NUM(ADDRESS) from TEST;
+---------+------------------------+---------------------+
| Contact | Address | FILTER_NUM(ADDRESS) |
+---------+------------------------+---------------------+
| A | 3995 Thomas Drive | Thomas Drive |
| B | 95 Dewberry Ct | Dewberry Ct |
| C | 635 mill st | mill st |
| D | 3050 E 6TH ST | E TH ST |
| E | 3216 Lawndale Ave | Lawndale Ave |
| F | 207 240th st. | th st. |
| G | 19 Lockhouse Rd. #14-3 | Lockhouse Rd. #- |
+---------+------------------------+---------------------+
7 rows in set (0.00 sec)
不过,这种方法效率不高,因为要对文本进行10次替换,如果文本很大,效率将非常低下,如果要更加高效,可以写更复杂的函数,来进行一次扫描,就完成全部字符串的替换。
转载于:https://blog.51cto.com/zhaizhenxing/134543