1、今天，有网友问我，怎么样过滤掉字符串中的数字，比如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. #- |+---------+---------------------+
2、马上想到使用mysql中字符串的replace功能来实现，但是查帮助后发现，replace只能对某个字符串进行替换，而无法对多个字符串替换，于是想到了一个笨办法，循环使用replace，如下：mysql> selectADDRESS,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)
3、当然，也可以写一个函数，以后遇到类似问题孀晏弁钾也可以使用：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)
4、不过，这种方法效率不高，因为要对文本进行10次替换，如果文本很大，效率将非常低下，如果要更加高效，可以写更复杂的函数，来进行一次扫描，就完成全部字符串的替换。