SQL经典实例（六）字符串处理

遍历字符串

例如，把emp表的ename等于KING的字符串拆开来显示为4行，每行一个字符。
借助表t10:

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);

其实看一下from语句的笛卡尔积就能理解：

select ename, iter.pos
    from (select ename from emp where ename = 'KING') e,
         (select id as pos from t10) iter;

嵌入引号

select 'g''day mate' qmarks from t1 union all
select 'beavers''teeth' from t1 union all
select '''' from t1;

统计字符出现的次数

select (length('HELLO HELLO') - 
        length(replace('HELLO HELLO', 'LL', '')))/length('LL')
    as cnt
from t1;

删除不想要的字符

例如，从emp表中的ename中删除元音字母，从sal中删除所有的0：
Oracle

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

MySQL没有提供translate函数，需要多次调用replace：

select ename,
       replace(
       replace(
       replace(
       replace(
       replace(ename, 'A', ''), 'E', ''), 'I', ''), 'O', ''), 'U', '') as stripped1,
       sal,
       replace(sal, 0, '') as stripped2
    from emp;

分离数字和字符数据

考虑如下结果集：

select ename || cast(sal as char(4)) as data from emp;

想把数字和字母分开成两列。
Oracle

select replace(
        translate(data, '0123456789', '0000000000'), '0', '') ename,
       to_number(
        replace(
          translate(lower(data),
            'abcdefghijklmnopqrstuvwxyz',
            rpad('z', 26, 'z')), 'z')) sal
    from (
    select ename || cast(sal as char(4)) data 
        from emp ) x;

rpad函数从右边对字符串使用指定的字符进行填充 　　
rpad(string,padded_length,[pad_string])
string 表示：被填充的字符串 　　
padded_length 表示：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符; 　　
pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。
例如：
rpad('tech', 7); 将返回'tech ' (后面有三个空格) 　　
rpad('tech', 2); 将返回'te' 　　
rpad('tech', 8, '0'); 将返回'tech0000' 　　
rpad('tech on the net', 15, 'z'); 将返回 'tech on the net' （原字符串一共15个，所以无需增加或者截取）　　
rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'

判断含有字母和数字的字符串

考虑如下视图：
Oracle

create view V as 
select ename as data
    from emp
  where deptno = 10
  union all 
select ename || ', $' || cast(sal as char(4)) || '.00' as data
    from emp
  where deptno = 20
  union all
select ename || cast(deptno as char(4)) as data
    from emp
  where deptno = 30;

MySQL

create view V as 
select ename as data
    from emp
  where deptno = 10
  union all 
select concat(ename, ', $', sal, '.00') as data
    from emp
  where deptno = 20
  union all
select concat(ename, deptno) as data
    from emp
  where deptno = 30;

想要过滤那些包含了除字母和数字之外的结果：

Oracle

select data from V
    where translate(lower(data),
        '0123456789abcdefghijklmnopqrstuvwxyz ',
        rpad('a', 37, 'a')) = rpad('a', length(data), 'a');

MySQL

select data from V
    where data regexp '[^0-9a-zA-Z]' = 0;

注意，由于创建视图V时使用的是char(4),长度是固定的，所以当cast(deptno as char(4))长度不足4时会用空格补齐。因此需要对空格进行处理。原书p96语句有误

创建分隔列表

考虑转换如下结果集：

select deptno, ename as emps from emp order by deptno;

变换成：

Oracle

select deptno,
       ltrim(sys_connect_by_path(ename, ','), ',') emps
    from (
select deptno, ename,
       row_number() over (partition by deptno order by empno) rn,
       count(*) over (partition by deptno) cnt
    from emp
    )
where level = cnt
    start with rn = 1
    connect by prior deptno = deptno and prior rn = rn-1;

MySQL

select deptno,
       group_concat(ename order by empno separator ',') as emps
    from emp
group by deptno;

GROUP_CONCAT函数可以完成所有的工作。它负责把传递给它的ENAME列拼接起来。它是一个聚合函数，所以查询语句需要用到GROUP_BY.

Oracle 语句分析：
考虑from子句的子查询：

select deptno, ename,
       row_number() over (partition by deptno order by empno) rn,
       count(*) over (partition by deptno) cnt
    from emp;

按字母表顺序排列字符

考虑如下将ename按照字母顺序排列如下：

MySQL

select ename old_ename, group_concat(c order by c separator '') new_ename
    from (
        select ename, substr(a.ename, iter.pos, 1) c
            from emp a,
                (select id pos from t10) iter
            where iter.pos <= length(a.ename)
        ) x
    group by ename;

考虑from子查询：

select ename, substr(a.ename, iter.pos, 1) c
            from emp a,
                (select id pos from t10) iter
            where iter.pos <= length(a.ename)

...
GROUP_CONCAT函数不仅能串接每个字母，还能按照字母表顺序对它们进行排序。

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

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);

分析：看里层的子查询：

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

...
然后，提取出排好序的字符并重建每个名字。使用SYS_CONNECT_BY_PATH函数来将所有的字符按顺序串接起来

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

...
最后只保留那些和原名字具有相同长度的字符串。

提取第n个分隔子字符串

考虑如下视图：

create view V as
select 'mo,larry,curly' as name
    from t1
  union all
select 'tina,gina,jaunita,regina,leena' as name
    from t1;

希望提取每一行的第二个名字，解决问题的关键是把每一个名字转换为单独的一行，并保持每一个名字在列表里的顺序不变。

MySQL

select name
  from (
select iter.pos,
       substring_index(
       substring_index(src.name, ',', iter.pos), ',', -1) name
  from V src,
       (select id pos from t10) iter
 where iter.pos <= 
            length(src.name) - length(replace(src.name, ',', ''))
    ) x
where pos = 2;

Oracle

select sub
  from (
select iter.pos,
       src.name,
       substr(src.name,
        instr(src.name, ',', 1, iter.pos) + 1,
        instr(src.name, ',', 1, iter.pos+1) - 
        instr(src.name, ',', 1, iter.pos)-1) sub
   from (select ','||name||',' as name from V) src,
        (select rownum pos from emp) iter
 where iter.pos < length(src.name) - length(replace(src.name, ','))
    )
where pos = 2;

《SQL经典实例》第六章