MySQL 判断字符串是不是数字字符类型

我就是我 2022-12-09 01:51 192阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb