6.1 遍历字符串

  1. 问题
    你想遍历一个字符串，并以一个字符一行的形式把它们显示出来，但 SQL 没有 Loop 循环功能。例如，你想把 EMP 表的 ENAME 等于 KING 的字符串拆开来显示为 4 行，每行一个字符。

substr函数 (俗称：字符截取函数)
substr(string string, int a, int b);
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度

select substr(ename,iter.pos,1)
from (select ename from emp where ename='KING') e,
(select id as pos from t1) iter
where iter.pos<=length(ename)

首先构造一个笛卡尔积，使用两个表做连接查询，t1表中的id列有10行，分别是从1到10，保证足够的循环次数，每次循环使用substr()函数提取对应的字符，pos每行递增保证连续提取字符，where子句限制循环了四次跳出，等于给的字符的长度。
这就是模拟循环的原理。

select ename, iter.pos
 from (select ename from emp where ename = 'KING') e,
 (select id as pos from t10) iter
ENAME POS
---------- ----------
KING 1
KING 2
KING 3
KING 4
KING 5
KING 6
KING 7
KING 8
KING 9
KING 10

6.2 嵌入引号

  1. 问题
    你想在字符串常量里嵌入引号，并且希望使用 SQL 产生如下所示的结果。
    QMARKS

g’day mate
beavers’ teeth
解决思路：

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

在任何字符串里引号的个数都应该是一个偶数。要想在字符串中间插入一个引号，需要使用两个引号才行。两个外层的引号用于定义一个字符串常量，在该字符串常量里还使用了两个引号来代表一个引号，我们实际上希望它显示的内容就是这一个引号。
理引号时，还要记住如果一个字符串里只包含两个引号，并且这两个引号中间没有任何
字符，那么这个字符串是 Null。即’’’'为空字符串。

6.3　统计字符出现的次数

  1. 问题
    你想统计某个字符或者子字符串在给定字符串里出现的次数，考虑如下的字符串。
    10,CLARK,MANAGER
    你想知道该字符串里有多少个逗号。

LENGTH(str)函数，计算字符串长度
REPLACE(str,from_str,to_str)函数，对字符串中的字符进行替换，from_str是被替换的字符，to_str是要替换的字符。

select (length('10,CLARK,MANAGER')-   #LENGTH函数获取字符串长度
       length(replace('10,CLARK,MANAGER',',','')))/length(',')   #用 LENGTH 函数获取不含逗号的字符串长度
as cnt     #相减的差值除以查找的字符串的长度得逗号的个数
from t1

如果被搜索的字符串的长度大于 1 的话，就必须使用除法运算。

6.4　删除不想要的字符

  1. 问题
    你想从你的数据里删除指定的字符。
    ENAME SAL
SMITH 800
 ALLEN 1600
 WARD 1250
 JONES 2975
 MARTIN 1250
 BLAKE 2850
 CLARK 2450
 SCOTT 3000
 KING 5000
 TURNER 1500
 ADAMS 1100
 JAMES 950
 FORD 3000
 MILLER 1300


你希望从上面的数据里删除所有的 0 和元音字母，并将删除后的值显示在 STRIPPED1 列和STRIPPED2 列中。

MySQL 和 SQL Server 没有提供 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

translate(string,from_str,to_str)函数
执行时,translate依次检查string中的每个字符是否在from_str中存在，如果不存在，那么这个string中的字符直接返回，如果存在，translate会记下这个字符在from_str中的位置，然后用to_str的同样位置的字符代替string中的这个字符作业返回结果。

sql中translate与replace 的区别
原文：

6.5 分离数字和字符数据

  1. 问题
    你把数字和字符数据混合存放进一列。你想把其中的数字数据和字符数据分开，
    考虑如下的结果集。
    DATA
SMITH800
 ALLEN1600
 WARD1250
 JONES2975
 MARTIN1250
 BLAKE2850
 CLARK2450
 SCOTT3000
 KING5000
 TURNER1500
 ADAMS1100
 JAMES950
 FORD3000
 MILLER1300
 你希望得到如下的结果集。
 ENAME SAL
SMITH 800
 ALLEN 1600
 WARD 1250
 JONES 2975
 MARTIN 1250
 BLAKE 2850
 CLARK 2450
 SCOTT 3000
 KING 5000
 TURNER 1500
 ADAMS 1100
 JAMES 950
 FORD 3000
 MILLER 1300