这个和字符串有关的SQL怎么写，谢谢

Question

表的介绍：
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谢谢大家，急用啊，谢谢了

Answer 1

长的一塌糊涂：select 
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穷举了。

Answer 2

把数字放在when里面，其他字符放在else里面更好一点，可以支持大小写不区分：select 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)>='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  

Answer 3

create or replace function smalltobig(f_example varchar2) return varchar2 is
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;用函数调用。