SQL.Cookbook学习笔记一（使用字符串）

虽然说SQL对字符串进行操作是非常繁琐和令人厌烦的，但对于专做数据处理的人来说学学还是非常必要的。

1.1 遍历字符串

问题
遍历一个字符，并将其中的每个字符都作为一行返回，但是SQL没有循环操作。例如，要将表EMP中ENAME值为“KING”的字符串显示为4行，每行中都包含“KING”中的一个字符。
解决方案
使用笛卡儿积来生成行号，用来在该行中返回字符串中的每个字符。然后，使用DBMS中的内置的字符串分析函数来摘出所要显示的字符(SQL Serer用户可使用SUBSTRING代替SUBSTR):

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

查询结果：

C
-
K
I
N
G

注:在DB2数据库中row_number() over() 是一个生成行号的函数（当然了在这里emp表数据行数必须大于4行）
接下来我们把整个SQL语句分解开来
1. e 和 iter的笛卡儿积

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

2. 限定结果集的行数

select ename, iter.pos
from (select ename from emp where ename = 'KING') e,
(select row_number() over() as pos from emp) iter
where iter.pos <= length(e.ename)

结果：

ENAME	POS
KING	1
KING	2
KING	3
KING	4

3. 截取相应位置的字符

select substr(e.ename,iter.pos) a,
        substr(e.ename,length(e.ename)-iter.pos+1) b
from (select ename from emp where ename = 'KING') e,
(select row_number() over() as pos from emp) iter
where iter.pos <= length(e.ename)

按照pos的值来截取ENAME中相应位置的字符
结果：

A	B
KING	G
ING	NG
NG	ING
G	KING

1.2 字符串文字中包含引号

SQL语句中单引号表示字符串，但有时需要在字符串中加入单引号，这时就需要转义，除去两头的两个单引号，在中间出现的单引号必须成对才表示一个单引号。
例：

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

结果：

QMARKS
--------------
g'day mate
beavers' teeth
'

学后小结：
在使用引号时，可以将它们当括号看待。如果有一个前括号，也必须有一个相应的后括号，这一原则也适用于引号。注意，在任何字符串中，必须保持引号个数为偶数。即若要在字符串中嵌人一个引号，必然会有第二个引号。在使用引号时，一定要记住字符串是由两个引号来定义的，而在两个引号中没有任何字符时，表示空字符串值。

1.3 计算字符在字符串中出现的次数

计算一个字符或子串在给定的字符串中出现的次数。考虑下面的字符串:
10,CLARK,MANAGER
要计算在这个字符串中有多少个逗号。
解决办法：
首先计算出原字符串的长度，然后计算去掉逗号后字符串的长度，这两者的差就是逗号在该字符串中出现的次数.每种DBMS都提供了求字符串长度以及从字符串中删除某个字符的函数。在多数情况下，这两个函数分别为LENGTH和REPLACE (SQL Server用户可以使用内置的LEN函数，而不是LENGTH)e：

select (length(',CLARK,MANAGER') - length(replace(',CLARK,MANAGER',',','')))/length(',')as cnt from t

注：这里length(‘,’)的目的是语句更具有普遍性，如果统计的是其它字符串的个数则需要计算要统计的字符串的长度。

1.4 从字符串中删除不需要的字符

有些时候数据中有规律地参杂一些没用的字符，我们需要把它删除。
从数据中删除指定的字符，考虑下列的结果集:

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

现在需要把ENAME中的元音字母删除，把SAL中的0删除。想要得到以下效果图：

解决方案：
每个DBMS都提供了函数用来从字符串中删除不需要的字符，对于本间题来说，常用的就是REPLACE和TRANSLATE函数。

• DB2数据库解决方案：

使用内置的TRANSLATE和REPLACE函数来删除不需要的字符或字符串

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

注：translate 是翻译函数，即把一些字符翻译成另外一些字符，在这里translate(ename,’aaaaa’,’AEIOU’) 是把字符串ename 中含有的元音字母（AEIOU）都翻译成小写的字母a,replace(translate(ename,’aaaaa’,’AEIOU’),’a’,”) 然后replace的作用是把翻译后的字符串中的a都用空字符串来代替。对于sal 这个字段先把它转换成字符类型，然后把其中的0用空字符串来替换。

• MySQL和SQL Server 解决方案

  MySQL和SQL Server不支持TRANSLATE函数，所以就需要多次调用REPLACE函数来执行此操作（最新版本不知道是否支持）：

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

• Oracle和PostgreSQL

使用内置的TRANSLATE和REPLACE函数来删除不需要的字符或字符串:

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

注：db2 中replace函数、translate函数和Oracle中的相应函数略有不同，工作中需要区分。