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

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

考虑如下视图：

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