关于字符串处理

1.遍历字符串(遍历emp表中的ename='KING'的值)

  select substr(e.ename,iter.pos,1) as c
  from (select ename from emp where ename = 'KING') e,
     (select id as pos from t10) iter
    where iter.pos <= length(e.ename)

 

2.字符串中带有带引号

   select 'to''day' from dual

 

3.计算字符在字符串中出现的次数(查询字符串Hello World中出现ll的次数)

  select (length('Hello World') - length(replace('Hello World','ll','')))/length('LL') from dual

 

4.删除不需要的字符

 select ename,replace(translate(ename,'AEIOU','aaaaa'),'a') as   stripped1,
sal,replace(sal,0,'') as stripped2 from emp

 

5.将数字和字符分开

  with temp as(
    select 'abdsc1234' data from dual
  )
 select replace(translate(data,'0123456789','0000000000'),'0') ename,
       to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@')) mm,
      to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z')) sal
from (select data from temp)     

 

6.提取大写首次母的名字缩写 

with temp as(
select 'Stewie Griffin' data from dual
)
select replace(translate(translate(data,' ','.'),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#') sx from temp

 

7.将分隔数据转换为多值IN列表

 select empno,ename,sal,deptno from emp
where empno in(
select to_number(rtrim(substr(emps,instr(emps,',',iter.pos) + 1 ,instr(emps,',',iter.pos + 1)-instr(emps,',',iter.pos) -1)))
 from (select ','||'7698,7654,7782,7788'||',' emps from dual) csv,
                      (select rownum pos from emp) iter                    
)

 

8.把某字段按字符排序每一个值(复杂)

 select old_name,new_name from(
 select old_name,replace(sys_connect_by_path(c,' '),' ') new_name
        from(select e.ename old_name,

        row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1)) rn,
        substr(e.ename,iter.pos,1) c
        from emp e,(select rownum pos from emp) iter
        where iter.pos <= length(e.ename) order by 1) x
        start with rn = 1 connect by prior rn = rn - 1 and prior old_name = old_name)
        where length(old_name) = length(new_name)

        

9. 把某一字段里的数字提取出来

   1)

     with temp as(
select 'aaa10bb' a from dual
union all
select 'aaa10' a from dual
union all
select '10bb' a from dual
union all
select '123' a from dual
union all
select 'adsb' a from dual
)
select to_number(
  case when replace(translate(a,'0123456789','9999999999'),'9') is not null
       then replace(translate(a,replace(translate(a,'0123456789','999999999'),'9'),rpad('#',length(a),'#')),'#')
       else a
       end ) a
   from temp
   where instr(translate(a,'0123456789','999999999'),'9') > 0   

 

2)with temp as(
select 'aaa10bb' a from dual
union all
select 'aaa10' a from dual
union all
select '10bb' a from dual
union all
select '123' a from dual
union all
select 'adsb' a from dual
)
select to_number(n) from(
select replace(translate(lower(a),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#') n from temp
) where n is not null

 

10. 取出以逗号分隔的字符串第二个字符串

 with temp as(
select 'mo,larry,curly' as name from dual
union all
select 'tina,gina,jaunita,regina,leena' as name from dual
)
select name,a,b,substr(name,a+1,b-1-a) from(
select name,instr(name,',') a,instr(name,',',instr(name,',')+1) b from temp
)

 

11.分解ip地址

 select ip,substr(ip,1,instr(ip,'.') - 1) a,
          substr(ip,instr(ip,'.') + 1,instr(ip,'.',1,2) - instr(ip,'.') - 1) b,
          substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3) - instr(ip,'.',1,2) - 1) c,
          substr(ip,instr(ip,'.',1,3) + 1) d
       from (select '92.111.0.2' as ip from dual)  

 

12.按符号分隔字符

with a as(
select 'aaa' name,'1a-12b-13c-22d' lx,2 sl,'jf' ty,'bm' bz from dual
union all
select 'aaa' name,'1a-12b-34d' lx,3 sl,'jff' ty,'bmz' bz from dual
union all
select 'bbb' name,'2a-4b-16d' lx,7 sl,'jfff' ty,'bmzz' bz from dual
)
, temp as(
select level lv from dual connect by level < 4
)
select name,min(lx) lx,jf,js,sx,sum(sl) sl,min(ty) ty,min(bz) bz from(
select name,lx,k1 jf,nvl(k2,substr(lx,lv1+1,length(lx))) js,lv sx,sl,ty,bz from(
select name,lx,lv0,lv1,lv2,k1,k2,lv,sl,ty,bz from(
select name,lx,lv0,lv1,lv2,substr(lx,lv0+1,lv1-lv0-1) k1,substr(lx,lv1+1,lv2-lv1-1) k2,lv,sl,ty,bz from(
select name,lx,decode(lv,1,0,instr(lx,'-',1,lv-1)) lv0,instr(lx,'-',1,lv) lv1,instr(lx,'-',1,lv+1) lv2,lv,sl,ty,bz from a,temp
) order by name,lx,lv
) where k1 is not null
)
)group by name,jf,js,sx order by name,lx,sx