1. SQL 并不专门用于处理复杂的字符串
1.1. 需要有逐字遍历字符串的能力。但是,使用SQL 进行这样的操作并不容易
1.2. SQL 没有Loop循环功能
1.2.1. Oracle的MODEL子句除外
2. 遍历字符串
2.1. 把EMP表的ENAME等于KING的字符串拆开来显示为4行,每行一个字符
2.2. sql
select substr(e.ename,iter.pos,1) as Cfrom (select ename from emp where ename = 'KING') e,(select id as pos from t10) iter
where iter.pos <= length(e.ename)
C
-
K
I
N
G
2.3. T10表,该表有10行记录(它只有一列,列名为ID,它的值分别是从1到10
3. 嵌入引号
3.1. sql
QMARKS
--------------
g'day mate
beavers' teeth
'
3.2. sql
select 'g''day mate' qmarks from t1 union all
select 'beavers'' teeth' from t1 union all
select '''' from t1
4. 统计字符出现的次数
4.1. 10,CLARK,MANAGER
4.1.1. 该字符串里有多少个逗号
4.2. sql
select (length('10,CLARK,MANAGER')-length(replace('10,CLARK,MANAGER',',','')))/length(',')as cntfrom t1
4.3. 获取不含逗号的字符串长度
4.4. 逗号的删除则借助了REPLACE函数
5. 删除不想要的字符
5.1. sql
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
5.2. sql
ENAME STRIPPED1 SAL STRIPPED2
--------- ---------- ---------- ---------
SMITH SMTH 800 8
ALLEN LLN 1600 16
WARD WRD 1250 125
JONES JNS 2975 2975
MARTIN MRTN 1250 125
BLAKE BLK 2850 285
CLARK CLRK 2450 245
SCOTT SCTT 3000 3
KING KNG 5000 5
TURNER TRNR 1500 15
ADAMS DMS 1100 11
JAMES JMS 950 95
FORD FRD 3000 3
MILLER MLLR 1300 13
5.3. DB2
5.3.1. sql
select ename,replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1,sal,replace(cast(sal as char(4)),'0','') stripped2from emp
5.4. Oracle
5.5. PostgreSQL
5.6. 使用内置函数TRANSLATE和REPLACE删除不想要的字符和字符串
5.6.1. sql
select ename,replace(translate(ename,'AEIOU','aaaaa'),'a')as stripped1,sal,replace(sal,0,'') as stripped2from emp
5.7. MySQL
5.8. SQL Server
5.9. 多次调用REPLACE 函数
5.9.1. sql
select ename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','')as stripped1,sal,replace(sal,0,'') stripped2from emp
6. 分离数字和字符数据
6.1. sql
DATA
---------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
6.2. DB2
6.2.1. sql
select replace(translate(data,'0000000000','0123456789'),'0','') ename,cast(replace(translate(lower(data),repeat('z',26),'abcdefghijklmnopqrstuvwxyz'),'z','') as integer) salfrom (select ename||cast(sal as char(4)) datafrom emp) x
6.3. Oracle
6.3.1. sql
select replace(translate(data,'0123456789','0000000000'),'0') ename,to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z')) salfrom (select ename||sal datafrom emp)
6.4. PostgreSQL
6.4.1. sql
select replace(translate(data,'0123456789','0000000000'),'0','') as ename,cast(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z','') as integer) as salfrom (select ename||sal as datafrom emp) x
7. 按照子字符串排序
7.1. sql
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
7.2. DB2
7.3. Oracle
7.4. PostgreSQL
7.5. MySQL
7.6. 使用内置函数LENGTH和SUBSTR
7.6.1. sql
select enamefrom emporder by substr(ename,length(ename)-1,2)
7.7. SQL Server
7.7.1. sql
select enamefrom emporder by substring(ename,len(ename)-1,2)
8. 根据字符串里的数字排序
8.1. sql
DATA
-----------------------------
CLARK 7782 ACCOUNTING
KING 7839 ACCOUNTING
MILLER 7934 ACCOUNTING
SMITH 7369 RESEARCH
JONES 7566 RESEARCH
SCOTT 7788 RESEARCH
ADAMS 7876 RESEARCH
FORD 7902 RESEARCH
ALLEN 7499 SALES
WARD 7521 SALES
MARTIN 7654 SALES
BLAKE 7698 SALES
TURNER 7844 SALES
JAMES 7900 SALES
8.2. DB2
8.2.1. sql
select datafrom Vorder bycast(replace(translate(data,repeat('#',length(data)),replace(translate(data,'##########','0123456789'),'#','')),'#','') as integer)
8.3. Oracle
8.3.1. sql
select datafrom Vorder byto_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#'))
8.4. PostgreSQL
8.4.1. sql
select datafrom Vorder bycast(replace(translate(data,replace(translate(data,'0123456789','##########'),'#',''),rpad('#',20,'#')),'#','') as integer)
9. 识别字符串里的数字字符
9.1. sql
MIXED
--------------
CL10AR
KI10NG
MI10LL
7369
7566
7788
7876
7902
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
9.2. DB2
9.2.1. sql
select mixed old,cast(casewhenreplace(translate(mixed,'9999999999','0123456789'),'9','') = ''thenmixedelse replace(translate(mixed,repeat('#',length(mixed)),replace(translate(mixed,'9999999999','0123456789'),'9','')),'#','')end as integer ) mixedfrom Vwhere posstr(translate(mixed,'9999999999','0123456789'),'9') > 0
9.3. Oracle
9.3.1. sql
select to_number (casewhenreplace(translate(mixed,'0123456789','9999999999'),'9')is not nullthenreplace(translate(mixed,replace(translate(mixed,'0123456789','9999999999'),'9'),rpad('#',length(mixed),'#')),'#')elsemixedend) mixedfrom Vwhere instr(translate(mixed,'0123456789','9999999999'),'9') > 0
9.4. PostgreSQL
9.4.1. sql
select cast(casewhenreplace(translate(mixed,'0123456789','9999999999'),'9','')is not nullthenreplace(translate(mixed,replace(translate(mixed,'0123456789','9999999999'),'9',''),rpad('#',length(mixed),'#')),'#','')elsemixedend as integer ) as mixedfrom Vwhere strpos(translate(mixed,'0123456789','9999999999'),'9') > 0
9.5. MySQL
9.5.1. sql
select cast(group_concat(c order by pos separator '') as unsigned)as MIXED1from (select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as cfrom V,( select id pos from t10 ) iterwhere iter.pos <= length(v.mixed)and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57) ygroup by mixedorder by 1
10. 提取第n个分隔子字符串
10.1. sql
create view V as
select 'mo,larry,curly' as namefrom t1union all
select 'tina,gina,jaunita,regina,leena' as namefrom t1
10.2. sql
SUB
-----
larrygina
10.3. DB2
10.3.1. sql
select substr(c,2,locate(',',c,2)-2)from (
select pos, name, substr(name, pos) c,row_number() over(partition by nameorder by length(substr(name,pos)) desc) rnfrom (
select ',' ||csv.name|| ',' as name,cast(iter.pos as integer) as posfrom V csv,(select row_number() over() pos from t100 ) iterwhere iter.pos <= length(csv.name)+2) xwhere length(substr(name,pos)) > 1and substr(substr(name,pos),1,1) = ',') ywhere rn = 2
10.4. Oracle
10.4.1. sql
select subfrom (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) subfrom (select ','||name||',' as name from V) src,(select rownum pos from emp) iterwhere iter.pos < length(src.name)-length(replace(src.name,',')))where pos = 2
10.5. PostgreSQL
10.5.1. sql
select namefrom (select iter.pos, split_part(src.name,',',iter.pos) as namefrom (select id as pos from t10) iter,(select cast(name as text) as name from v) srcwhere iter.pos <=length(src.name)-length(replace(src.name,',',''))+1) xwhere pos = 2
10.6. MySQL
10.6.1. sql
select namefrom (select iter.pos,substring_index(substring_index(src.name,',',iter.pos),',',-1) namefrom V src,(select id pos from t10) iter,where iter.pos <=length(src.name)-length(replace(src.name,',',''))) xwhere pos = 2
10.7. SQL Server
10.7.1. sql
select substring(c,2,charindex(',',c,2)-2)from (select pos, name, substring(name, pos, len(name)) as c,row_number() over(partition by nameorder by len(substring(name,pos,len(name))) desc) rnfrom (select ',' + csv.name + ',' as name,iter.posfrom V csv,(select id as pos from t100 ) iterwhere iter.pos <= len(csv.name)+2) xwhere len(substring(name,pos,len(name))) > 1and substring(substring(name,pos,len(name)),1,1) = ',') ywhere rn = 2
11. 解析IP地址
11.1. 111.22.3.4
11.2. sql
A B C D
----- ----- ----- ---
111 22 3 4
11.3. DB2
11.3.1. sql
with x (pos,ip) as (values (1,'.92.111.0.222')union allselect pos+1,ip from x where pos+1 <= 20)select max(case when rn=1 then e end) a,max(case when rn=2 then e end) b,max(case when rn=3 then e end) c,max(case when rn=4 then e end) dfrom (select pos,c,d,case when posstr(d,'.') > 0 then substr(d,1,posstr(d,'.')-1)else dend as e,row_number() over(order by pos desc) rnfrom (select pos, ip,right(ip,pos) as c, substr(right(ip,pos),2) as dfrom xwhere pos <= length(ip)and substr(right(ip,pos),1,1) = '.') x) y
11.4. Oracle
11.4.1. sql
select ip,substr(ip, 1, instr(ip,'.')-1 ) a,substr(ip, instr(ip,'.')+1,instr(ip,'.',1,2)-instr(ip,'.')-1 ) b,substr(ip, instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1 ) c,substr(ip, instr(ip,'.',1,3)+1 ) dfrom (select '92.111.0.2' as ip from t1)
11.5. PostgreSQL
11.5.1. sql
select split_part(y.ip,'.',1) as a,split_part(y.ip,'.',2) as b,split_part(y.ip,'.',3) as c,split_part(y.ip,'.',4) as dfrom (select cast('92.111.0.2' as text) as ip from t1) as y
11.6. MySQL
11.6.1. sql
select substring_index(substring_index(y.ip,'.',1),'.',-1) a,substring_index(substring_index(y.ip,'.',2),'.',-1) b,substring_index(substring_index(y.ip,'.',3),'.',-1) c,substring_index(substring_index(y.ip,'.',4),'.',-1) dfrom (select '92.111.0.2' as ip from t1) y
11.7. SQL Server
11.7.1. sql
with x (pos,ip) as (select 1 as pos,'.92.111.0.222' as ip from t1union allselect pos+1,ip from x where pos+1 <= 20)select max(case when rn=1 then e end) a,max(case when rn=2 then e end) b,max(case when rn=3 then e end) c,max(case when rn=4 then e end) dfrom (select pos,c,d,case when charindex('.',d) > 0then substring(d,1,charindex('.',d)-1)else dend as e,row_number() over(order by pos desc) rnfrom (select pos, ip,right(ip,pos) as c,substring(right(ip,pos),2,len(ip)) as dfrom xwhere pos <= len(ip)and substring(right(ip,pos),1,1) = '.') x) y