Oracle经典实例——字符串处理

1.遍历字符串

遍历一个字符串，使用笛卡尔积生成每行一个字符的形式显示所需要的行数，然后使用数据库内置字符串解析函数提取想要的结果：
每行显示一个字符串

select substr(a.ename, b.rn, 1) c
  from (select 'KING' ename from dual) a,
       (select rownum rn from dual connect by rownum <= 10) b
 where b.rn <= length(a.ename);

每行显示不同的字符串

select substr(a.ename, b.rn) str1,
       substr(a.ename, length(a.ename) - b.rn + 1) str2
  from (select 'KING' ename from dual) a,
       (select rownum rn from dual connect by rownum <= 10) b
 where b.rn <= length(a.ename);

2.嵌入引号

使用''在字符串常量里插入引号：

select 'apples core' a,'apple''s core' b from dual;

在处理字符串时，如果一个字符串里只包含两个引号，且这两个引号中间没有任何字符，那么这个字符串是null

3.统计字符出现的次数

统计某个字符串或者子字符串在给定字符串里出现的次数：

 select (length('10,hello,world') -
       length(replace('10,hello,world', ',', ''))) / length(',') cnt
  from dual;

如果不使用最后的除法运算，会得到错误结果：

 select (length('hello hello') - length(replace('hello hello', 'll', ''))) error_cnt,
       (length('hello hello') - length(replace('hello hello', 'll', ''))) /
       length('ll') correct_cnt
  from dual;

4.删除不想要的字符

删除字符串中的元音字母：

select replace(translate('abgidsgiosnu','aeiou','aaaaa'),'a') str from dual;

其中TRANSLATE函数的作用是一次替换多个单个的字符

5.分离数字和字符数据

使用内置函数REPLACE和TRANSLATE来分离字符数据和数字数据：

select str,
       to_number(replace(translate(lower(str),
                 'abcdefghijklmnopqrstuvwxyz',
                 rpad('z', 26, 'z')),'z')) sal
  from (select 'sales500' str from dual);

其中RPAD函数的作用是从右边对字符串使用指定的字符进行填充，即快速产生26个z字母
分离字符数据方法同上

5.提取姓名的首字母

使用内置函数REPLACE、TRANSLATE和RPAD提取首字母

 select replace(replace(
       translate(
               replace('Russell Westbrook','.',''),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#',''),' ','.')||'.'
                  from dual;

6.按照子字符串排序

使用SUBSTR函数提取出用作排序标准的子字符串，然后进行排序：
/按field1字段的开始2个字符进行排序/

select field1,field2 from table_name order by substr(field1,1,2); 

7.根据字符串里的数字进行排序

使用内置函数REPLACE、TRANSLATE和RPAD提取数字数据，然后进行排序：

select field1 from table_name order by 
to_number(replace(
  translate(field1,
    replace(
     translate(field1,'0123456789','#########'),'#'),rpad('#',length(field1),'#')),'#')); 

8.创建分隔列表

把多行数据转换为以逗号分隔的一行列表：

select field1, ltrim(sys_connect_by_path(field2,','), ',') f2
  from (select field1,
               field2,
               row_number() over(partition by field1 order by field2) rn,
               count(*) over(partition by field1) cnt
          from table_name)
 where level = cnt
 start with rn = 1
connect by prior field1 = field1
       and prior rn = rn - 1;

其中使用LTRIM函数删除左边出现的逗号，使用SYS_CONNECT_BY_PATH创建分隔列表，
ROW_NUMBER() OVER (PARTITION BY FIELD1 ORDER BY FIELD2)表示根据FIELD1分组，
在分组内部根据FIELD2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)，
使用COUNT() OVER()对分组进行求和，当LEVE=CNT的时候就生成了一个完整的列表

9.按字母表顺序排列字符

使用SYS_CONNECT_BY_PATH函数能迭代地创建一个列表：

select old_name, new_name
  from (select old_name, replace(sys_connect_by_path(c, ' '), ' ') new_name
          from (select 'BOYGRIL' old_name,
                       row_number() over(partition by 'BOYGRIL' order by substr('BOYGRIL', iter.pos, 1)) rn,
                       substr('BOYGRIL', iter.pos, 1) c
                  from dual, (select rownum pos from dual connect by rownum <= 10) iter
                 where iter.pos <= length('BOYGRIL')
                 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);

其中最重要的工作是由内嵌视图x来完成的，它提取每个字符并按字母表顺序排列好，
剩下的查询语句只是将排好序的字符拼接到一起

10.识别字符串里的数字字符

使用REPLACE和TRANSLATE不断替换删除，最后得出数字字符：

select to_number(case
                   when replace(translate('abc1234def', '0123456789', '9999999999'),'9') is not null then
                    replace(translate('abc1234def',
                                      replace(translate('abc1234def','0123456789', '999999999'),'9'),
                                      rpad('#', length('abc1234def'), '#')),'#')
                   else 'abc1234def' end) str
  from dual where instr(translate('abc1234def','0123456789','9999999999'),'9')>0

---

整理自《SQL经典实例》