虽然说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中的相应函数略有不同,工作中需要区分。