第六章 使用字符串
6.1 遍历字符串
建表
create table t10(
num number(10)
)
插入数据(当做索引)
declare
v_index number(2);
begin
for v_index in 1..10 loop
insert into t10(num) values(v_index);
end loop;
end;
遍历字符串
select substr(e.ename, iter.pos)
from (select ename from emp where ename='KING') e,
(select num as pos from t10) iter
where iter.pos <= length(e.ename)
6.3 计算字符在字符串中出现的次数
10,CLARK,MANAGER 计算这个字符串中出现多少次逗号
select (
(length('10,CLARK,MANAGER') - length(replace('10,CLARK,MANAGER', ',', ''))) / length(',')
) as cnt from dual
6.4 从字符串中删除不需要的字符
将emp表中ename的元音字母删除,sal中的0删除
select ename, replace(translate(ename, 'AEIOU', 'aaaaa'), 'a', '') as stripped1,
sal, replace(sal, 0, '') as stripped2
from emp
6.5 将字符和数字数据分离
select data, replace(translate(data, '0123456789', '0'), '0', '') ename,
to_number(
replace(
translate(lower(data), 'abcdefghijklmnopqrstuvwxyz', rpad('z', 26, 'z')),
'z', '')
)
from (select ename || sal data from emp)
6.6 提取姓名的大写首字母缩写
将全名转换成大写首字母缩写,考虑Stewie Griffin
select replace (replace (translate(
'Stewie Griffin', 'abcdefghijklmnopqrstuvwxyz', rpad('#', 26, '#')
), '#', ''), ' ', '.')
from dual