备注:测试数据库版本为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)