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.