select item, value, tags
from misc
where instr(translate(item,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX'), '9') >0
只写了大写字母的,小写字母自己加一下吧!
from misc
where instr(translate(item,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX'), '9') >0
只写了大写字母的,小写字母自己加一下吧!
SQL> select * from testaa;ITEM VALUE TAGS
-------------------- --------- --------------------
vUde 33 UUD_scx
uDew 10 UUC_fcs
1123 102 XUC_fld
xC03 102 DES_gld
04CVLL 77 VUD_ddxSQL> create or replace function test_testaa(p_item in varchar2) return varchar2 as
2 v_in varchar2(20);
3 ilen number(5);
4 v_temp varchar2(5);
5 p_out number(1);
6 begin
7 v_in:=p_item;
8 ilen:=length(v_in);
9 for i in 1..ilen-1 loop
10 v_temp:=substr(v_in,i,1);
11 if ((ascii(v_temp) between 65 and 90) or (ascii(v_temp) between 97 and 122)) then
12 p_out:=0;
13 else
14 p_out:=1;
15 end if;
16 if p_out=1 then
17 return 'ÓÐÊý×Ö';
18 exit;
19 end if;
20 end loop;
21 return v_in;
22 end;
23 /Function created.SQL> select * from testaa a where a.item=test_testaa(a.item);ITEM VALUE TAGS
-------------------- --------- --------------------
vUde 33 UUD_scx
uDew 10 UUC_fcsSQL>
v_in varchar2(20);
ilen number(5);
v_temp varchar2(5);
p_out number(1);
begin
v_in:=p_item;
ilen:=length(v_in);
for i in 1..ilen-1 loop
v_temp:=substr(v_in,i,1);
if ((ascii(v_temp) between 65 and 90) or (ascii(v_temp) between 97 and 122)) then
p_out:=0;
else
p_out:=1;
end if;
if p_out=1 then
return '有数字';
exit;
end if;
end loop;
return v_in;
end;
i integer;
isEn number;
c char;
begin
-- Test statements here
isEn:=0;
for i in 1..length(item) loop
c:=lower(substr(item,i,1));
if INSTR('abcdefghijklmnopqrstuvwxyz',c) <> 0 then
isEn:=-1;
exit;
end if;
end loop;
return isEn;
end;