第四章 处理字符串

4.1 走查字符串

想遍历一个字符串，将其中的每个字符都作为一行返回，但 SQL 没有提供循环操作。例如，你想分 4行显示 EMP 表中的 ENAME"KING"，每一行只包含其中的一个字符。

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)

解析：

1、要遍历字符串的字符，关键是要连接到一张表，该表包含足够多的行，使得能够执行所需的迭代次数。这里使用的是 T10 表，它包含 10 行数据（这张表只有 ID 列，其中存储了从 1 到 10 的值）。这个查询最多只能返回 10 行。

2、为了在返回 4 行数据后退出循环，该解决方案使用了一个 WHERE 子句。为了让结果集包含的行数与姓名包含的字符数相同，这个 WHERE 子句指定了 ITER.POS <= LENGTH(E. ENAME) 作为条件。

3、在 E.ENAME 中的每个字符都有对应的一行后，可以将 ITER.POS 作为 SUBSTR 的参数，以遍历字符串中的字符。由于每一行的 ITER.POS 值都比前一行大 1，因此可以使用它来返回 E.ENAME 中的下一个字符。这就是该解决方案的工作原理。

4.2 计算字符串中特定字符出现的次数

想计算特定的字符或子串在给定字符串中出现的次数：10,CLARK,MANAGER

删除字符串中的逗号，再将原来的字符串长度与删除逗号后的字符串长度相减，就可以确定字符串中包含多少个逗号。所有 DBMS 都提供了获取字符串长度的函数以及从字符串中删除字符的函数。在大多数情况下，这两个函数分别是 LENGTH 和 REPLACE

select (length('10,CLARK,MANAGER')-length(replace('10,CLARK,MANAGER',',','')))/
        length(',')as cnt4   
from t1

通过将上述两个长度相减，得到的差值就是字符串包含的逗号个数。最后一个操作是用长度差值除以查找的字符串的长度，仅当要查找的字符串的长度大于 1 时，这个除法运算才是必不可少的。

4.3 将不想要的字符从字符串中删除

将特定的字符从数据中删除。一种使用场景是，处理格式糟糕的数值数据（尤其是金额数据）。在金额数据中，逗号被用作了千分位分隔符，其中还包含货币符号。另一种使用场景是，你要将数据库中的数据导出为 CSV 文件，但有一个文本字段包含逗号（访问 CSV 文件时，将把逗号视为分隔符）。

将其中所有的 0 和元音字母都删除：使用内置函数 TRANSLATE 和 REPLACE 删除不想要的字符和子串。

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

4.4 将数字数据和字符数据分开

同一列中同时存储了数字数据和字符数据。如果你使用的是同时存储了数量和度量单位（或货币符号）的遗留数据（例如，在列中存储 100 km、AUD$200 或 40 pounds，而不是将数量和单位存储在不同的列中），那么很可能会遇到这种情况。

使用内置函数 TRANSLATE 和 REPLACE 将字符数据和数字数据分开。与本章的其他实例一样，诀窍是使用函数 TRANSLATE 将多种字符转换为特定的字符，这样无须搜索多个数字或字符，而只需搜索表示所有数字或字符的字符。

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

4.5 判断字符串是否只包含字母和数字

想从一张表中返回这样的行，即其特定列只包含字母和数字

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

4.6 根据部分字符串排序

根据部分字符串排序

根据姓名的最后两个字符对记录进行排序。结合使用内置函数 LENGTH 和 SUBSTR 提取字符串的特定部分，并将其作为排序依据。

select ename
from emp  
order by substr(ename,length(ename)-1,2)