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经典实例》
