mysql 判断 数字_MySQL 判断字符串是不是数字字符类型

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:

scott建表及录入数据sql脚本

一.需求

需要判断字符串中是否只包含数字和字符，如果包含其他的，不显示数据

二.解决方案

这类需求，只能考虑使用正则表达式来解决

代码

create view v as

select ename as data

from emp

where deptno = 10

union all

select concat(ename,',$',cast(sal as char(4) ),'.00') as data

from emp

where deptno = 20

union all

select concat(ename,',$',cast(deptno as char(4))) as data

from emp

where deptno = 30;

select data

from V

where data regexp '[^0-9a-zA-Z]' = 0;

测试记录:

mysql> drop view v ;

Query OK, 0 rows affected (0.01 sec)

mysql> create view v as

-> select ename as data

-> from emp

-> where deptno = 10

-> union all

-> select concat(ename,',$',cast(sal as char(4) ),'.00') as data

-> from emp

-> where deptno = 20

-> union all

-> select concat(ename,',$',cast(deptno as char(4))) as data

-> from emp

-> where deptno = 30

-> ;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from v;

+----------------+

| data |

+----------------+

| CLARK |

| KING |

| MILLER |

| SMITH,$800..00 |

| JONES,$2975.00 |

| SCOTT,$3000.00 |

| ADAMS,$1100.00 |

| FORD,$3000.00 |

| ALLEN,$30 |

| WARD,$30 |

| MARTIN,$30 |

| BLAKE,$30 |

| TURNER,$30 |

| JAMES,$30 |

+----------------+

14 rows in set, 7 warnings (0.01 sec)

mysql>

mysql> select data

-> from V

-> where data regexp '[^0-9a-zA-Z]' = 0;

+--------+

| data |

+--------+

| CLARK |

| KING |

| MILLER |

+--------+

3 rows in set, 7 warnings (0.00 sec)