将mysql去掉字段中数字_mysql中怎么样过滤字符串中的数字

当然，也可以写一个函数，以后遇到类似问题也可以使用：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)