大侠帮忙看下--判别字符串是不是字符数字类型的

select data,translate(lower(data),

    '1234567890qwertyuiopasdfghjklzxcvbnm',

    rpad('a',36,'a') )  translated

  , rpad('a',length(data),'a') fixed

from V

    data        translated           fixed

ADAMS$,1100.00	aaaaa$,aaaa.aa	aaaaaaaaaaaaaa

CLARK	aaaaa	aaaaa

FORD$,3000.00	aaaa$,aaaa.aa	aaaaaaaaaaaaa

JONES$,2975.00	aaaaa$,aaaa.aa	aaaaaaaaaaaaaa

KING	aaaa	aaaa

MILLER	aaaaaa	aaaaaa

SCOTT$,3000.00	aaaaa$,aaaa.aa	aaaaaaaaaaaaaa

SMITH$,800 .00	aaaaa$,aaa .aa	aaaaaaaaaaaaaa

ALLEN30  	aaaaaaa  	aaaaaaaaa

WARD30  	aaaaaa  	aaaaaaaa

MARTIN30  	aaaaaaaa  	aaaaaaaaaa

BLAKE30  	aaaaaaa  	aaaaaaaaa

TURNER30  	aaaaaaaa  	aaaaaaaaaa

JAMES30  	aaaaaaa  	aaaaaaaaa



我用下面语句查询只得到了3个结果 部门号为30的结果没显示出来  上面的查询显示字符串的长度怎么会不一样

select data

from V

where translate(lower(data),

    '0123456789abcdefghijklmnopqrstuvwxyz',

    rpad('a',36,'a'))=rpad('a',length(data),'a')



CLARK

KING

MILLER

------解决方案--------------------
select data,lengthb(translate(lower(data),
    '1234567890qwertyuiopasdfghjklzxcvbnm',
    rpad('a',36,'a') ))  translated
  , lengthb(rpad('a',length(data),'a')) fixed
from V
看下长度
------解决方案--------------------
现在人气不行了 又看回来咯  还是举个例子吧

将数字字符替换成空  如果整体为空 那这字符串就是字符数字类型 如果不为空 就夹杂其他特殊字符

with t1 as

(

     select 'asdf234' c1 from dual

     union all

     select 'asdf334#@sdf' from dual

     union all

     select 'a23423ds' from dual

     union all

     select 'a<>?sdf' from dual

)



select c1 

from t1

where REGEXP_REPLACE(c1,'[0-9
------解决方案--------------------
a-z
------解决方案--------------------
A-Z]','') is null;





      c1

-----------------------------

1	asdf234

2	a23423ds