删除字段里面一些类似的问题

Question

mysql> select *  from a where length(phoneNo)>11 limit 5;
+-------------------+--------+---------+----------+-
-----------------+
| phoneNo           | name   | VIPtype | phoneFee | 
                 |
+-------------------+--------+---------+----------+
-----------------+
| 1.3826218338e+011 | 文   | NULL    | -1       |
| 13903079527.3762  | 曾 | NULL    | -1       | 
| 1.3002018189e+011 | 翁 | NULL    | -1       | 
| 1.3632266032e+011 | 强 | NULL    | -1       | 
+-------------------+--------+---------+----------+----------+------------------
-----------------+
4 rows in set (0.08 sec)
我想知道 怎么删除 phoneNo里面的'.'和‘e’还有‘+’这几个字符 一共有这么多
mysql> select count(*) from a where length(phoneNo)>11;
+----------+
| count(*) |
+----------+
|    84161 |
+----------+
1 row in set (16.63 sec)
我希望 phoneNo 里面的1.3002018189e+011变成130020189011这类的结构

Answer 1

REPLACE(REPLACE(REPLACE(1.3002018189e+011,'.',''),'e',''),'+','')

Answer 2

mysql> select replace(replace(replace('1.3826218338e+011','.',''),'e',''),'+','');
+---------------------------------------------------------------------+
| replace(replace(replace('1.3826218338e+011','.',''),'e',''),'+','') |
+---------------------------------------------------------------------+
| 13826218338011                                                      |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 3

不是只单个这个这一行 是 这一类的进行update。。一共有 84161  这么多
mysql> select count(*) from a where length(phoneNo)>11; 
+----------+ 
| count(*) | 
+----------+ 
|    84161 | 
+----------+ 
1 row in set (16.63 sec) 

Answer 4

update a 
set phoneNo=replace(replace(replace(phoneNo,'.',''),'e',''),'+','')
where length(phoneNo)>11;

Answer 5

update tt set f1=REPLACE(REPLACE(f1,'.',''),'e',''),'+','')

Answer 6

mysql> select * from gz where length(phoneNo) >11 order by rand() limit 6;
+--------------------------+
| phoneNo                  |
| 86194547 86194780(传真） | 
| 13711535275/26293270  |
| 84308030/13711660698  | 
| "13808873008          | 
| 13326488259 87791870  | 
| 13711026973845063     | +--------------------------想问下 这些号码中 怎么提取出手机号码 ？