- SQL code
select data,translate(lower(data), '1234567890qwertyuiopasdfghjklzxcvbnm', rpad('a',36,'a') ) translated , rpad('a',length(data),'a') fixedfrom V data translated fixedADAMS$,1100.00 aaaaa$,aaaa.aa aaaaaaaaaaaaaaCLARK aaaaa aaaaaFORD$,3000.00 aaaa$,aaaa.aa aaaaaaaaaaaaaJONES$,2975.00 aaaaa$,aaaa.aa aaaaaaaaaaaaaaKING aaaa aaaaMILLER aaaaaa aaaaaaSCOTT$,3000.00 aaaaa$,aaaa.aa aaaaaaaaaaaaaaSMITH$,800 .00 aaaaa$,aaa .aa aaaaaaaaaaaaaaALLEN30 aaaaaaa aaaaaaaaaWARD30 aaaaaa aaaaaaaaMARTIN30 aaaaaaaa aaaaaaaaaaBLAKE30 aaaaaaa aaaaaaaaaTURNER30 aaaaaaaa aaaaaaaaaaJAMES30 aaaaaaa aaaaaaaaa我用下面语句查询只得到了3个结果 部门号为30的结果没显示出来 上面的查询显示字符串的长度怎么会不一样select datafrom Vwhere translate(lower(data), '0123456789abcdefghijklmnopqrstuvwxyz', rpad('a',36,'a'))=rpad('a',length(data),'a')CLARKKINGMILLER------解决方案--------------------------------------------------------
select data,lengthb(translate(lower(data),
'1234567890qwertyuiopasdfghjklzxcvbnm',
rpad('a',36,'a') )) translated
, lengthb(rpad('a',length(data),'a')) fixed
from V
看下长度
------解决方案--------------------------------------------------------
现在人气不行了 又看回来咯 还是举个例子吧
将数字字符替换成空 如果整体为空 那这字符串就是字符数字类型 如果不为空 就夹杂其他特殊字符
- SQL code
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 t1where REGEXP_REPLACE(c1,'[0-9|a-z|A-Z]','') is null; c1-----------------------------1 asdf2342 a23423ds