表的介绍:
id example
1 1,5,8
1 1,2,b
3 9,10,4
. .
. .
. .要求:
我要把查询的数字变成英文+finger,字母变成大写+plam,中间按“,”隔开例子:
比如我
select example from table_name where id = 1
结果应该如下:
1 one finger,five finger,eigth finger
1 one finger,two finger,B plam谢谢大家,急用啊,谢谢了
id example
1 1,5,8
1 1,2,b
3 9,10,4
. .
. .
. .要求:
我要把查询的数字变成英文+finger,字母变成大写+plam,中间按“,”隔开例子:
比如我
select example from table_name where id = 1
结果应该如下:
1 one finger,five finger,eigth finger
1 one finger,two finger,B plam谢谢大家,急用啊,谢谢了
decode(substr(example,1,instr(example,',')-1),'1','one','2','two','3','three','4','four','5','five','6','six','7','seven','8','eight','9','nine','ten')||' figer,'||
decode(substr(example,instr(example,',')+1,instr(example,',',1,2)-1-instr(example,',')),'1','one','2','two','3','three','4','four','5','five','6','six','7','seven','8','eight','9','nine','ten')||' figer,'||
case when substr(example,instr(example,',',1,2)+1)>'a' and substr(example,instr(example,',',1,2)+1)<'z' then upper(substr(example,instr(example,',',1,2)+1))||' plam'
else decode(substr(example,instr(example,',',1,2)+1),'1','one','2','two','3','three','4','four','5','five','6','six','7','seven','8','eight','9','nine','ten')||' figer' end case
from table_name; 说明:
1.如果每一栏都有可能包含字符,那么都需要用case when先判断一下,上面只有第三栏(按照你的数据)用了case when。
2.数字如果不仅仅包含1-10,那么就无法用decode穷举了。
decode(substr(example,instr(example,',')+1,instr(example,',',1,2)-1-instr(example,',')),'1','one','2','two','3','three','4','four','5','five','6','six','7','seven','8','eight','9','nine','ten')||' figer,'||
case when substr(example,instr(example,',',1,2)+1)>='1' and substr(example,instr(example,',',1,2)+1)<='9' or substr(example,instr(example,',',1,2)+1)='10' then
decode(substr(example,instr(example,',',1,2)+1),'1','one','2','two','3','three','4','four','5','five','6','six','7','seven','8','eight','9','nine','ten')||' figer' else upper(substr(example,instr(example,',',1,2)+1))||' plam' end case
from ggg
v_a varchar2(100);
v_b varchar2(100);
v_c varchar2(100);
v_d varchar2(100);
v_num number;
begin
v_c:='';
v_d:='';
v_b:=f_example;
v_num :=instr(v_b,',');
loop
v_a :=substr(v_b,1,v_num-1);
if v_num=0 then
v_a:=v_b;
end if;
case v_a when '1' then v_a:='one finger';
when '2' then v_a:='two finger';
when '3' then v_a:='three finger';
when '4' then v_a:='four finger';
when '5' then v_a:='five finger';
when '6' then v_a:='six finger';
when '7' then v_a:='seven finger';
when '8' then v_a:='eight finger';
when '9' then v_a:='nine finger';
when '10' then v_a:='ten finger';
else v_a:=upper(v_a)||' finger' ;
end case;
v_c:=v_c||v_d||v_a;
exit when v_num=0;
v_d:=',';
v_b:=substr(v_b,v_num+1);
v_num :=instr(v_b,',');
end loop;
return v_c;
exception
when others then
return null;
end;用函数调用。