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

[byncz]

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

[yejihui9527]

select data,lengthb(translate(lower(data),
'1234567890qwertyuiopasdfghjklzxcvbnm',
rpad('a',36,'a') )) translated
, lengthb(rpad('a',length(data),'a')) fixed
from V
看下长度

[大明湖畔_帅锅]

正则判断不是更好么？

[coolkisses]

可以用 正则表达式 判断 != [0-9] , 即 字符

[byncz]

[Quote=引用 1 楼 的回复:]

select data,lengthb(translate(lower(data),
'1234567890qwertyuiopasdfghjklzxcvbnm',
rpad('a',36,'a') )) translated
, lengthb(rpad('a',length(data),'a')) fixed
from V
看下长度
[/Quote]

ADAMS$,1100.00 14 14
CLARK 5 5
FORD$,3000.00 13 13
JONES$,2975.00 14 14
KING 4 4
MILLER 6 6
SCOTT$,3000.00 14 14
SMITH$,800 .00 14 14
ALLEN30 9 9
WARD30 8 8
MARTIN30 10 10
BLAKE30 9 9
TURNER30 10 10
JAMES30 9 9

长度 一样啊，带标点和特殊符号的应该不一样才对

[byncz]

[Quote=引用 3 楼 的回复:]

可以用 正则表达式 判断 != [0-9] , 即 字符
[/Quote]

是包括字符和数字，不包括标点和特殊符号

[zoomer0818]

o 路过路过

[大明湖畔_帅锅]

现在人气不行了 又看回来咯 还是举个例子吧

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

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