ORACLE 判别字符串是不是字母数字型

来源：互联网发布：lc301编辑器源码编辑：程序博客网时间：2024/05/16 05:51

SQL> create view V
2 as
3 select ename as data
4 from emp
5 where deptno=10
6 union all
7 select ename||', $'||cast(sal as varchar2(10))||'.00' as data
8 from emp
9 where deptno=20
10 union all
11 select ename||cast(sal as varchar2(20)) as data
12 from emp
13 where deptno=30
14 ;

View created.

SQL> select * from V;

DATA
------------------------------
CLARK
KING
MILLER
SMITH, $800.00
JONES, $2975.00
SCOTT, $3000.00
ADAMS, $1100.00
FORD, $3000.00
ALLEN1600
WARD1250
MARTIN1250

DATA
------------------------------
BLAKE2850
TURNER1500
JAMES950

14 rows selected.

现在要求查询出为字母数字型的行，也就是说3-7要去掉，因为其中除了字母和数字还包含了其他字符。

SQL> select data
2 from V
3 where translate(lower(data),'0123456789abcdefghijklmnopqrstuvwxyz',rpad('z',36,'z'))=
4 rpad('z',length(data),'z');

DATA
------------------------------
CLARK
KING
MILLER
ALLEN1600
WARD1250
MARTIN1250
BLAKE2850
TURNER1500
JAMES950

9 rows selected.