修改oracle字符集简书,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经典实例》