 # 1. SQL 並不專門用於處理複雜的字元串 ## 1.1. 需要有逐字遍歷字元串的能力。但是，使用SQL 進 ...
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 C
from (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 cnt
from 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','') stripped2
from 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 stripped2
from 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,'') stripped2
from 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) sal
from (
select ename||cast(sal as char(4)) data
from 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')) sal
from (
select ename||sal data
from 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 sal
from (
select ename||sal as data
from 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 ename
from emp
order by substr(ename,length(ename)-1,2)
7.7. SQL Server
7.7.1. sql
select ename
from emp
order 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 data
from V
order by
cast(
replace(
translate(data,repeat('#',length(data)),
replace(
translate(data,'##########','0123456789'),
'#','')),'#','') as integer)
8.3. Oracle
8.3.1. sql
select data
from V
order by
to_number(
replace(
translate(data,
replace(
translate(data,'0123456789','##########'),
'#'),rpad('#',20,'#')),'#'))
8.4. PostgreSQL
8.4.1. sql
select data
from V
order by
cast(
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(
case
when
replace(
translate(mixed,'9999999999','0123456789'),'9','') = ''
then
mixed
else replace(
translate(mixed,
repeat('#',length(mixed)),
replace(
translate(mixed,'9999999999','0123456789'),'9','')),
'#','')
end as integer ) mixed
from V
where posstr(translate(mixed,'9999999999','0123456789'),'9') ＞ 0
9.3. Oracle
9.3.1. sql
select to_number (
case
when
replace(translate(mixed,'0123456789','9999999999'),'9')
is not null
then
replace(
translate(mixed,
replace(
translate(mixed,'0123456789','9999999999'),'9'),
rpad('#',length(mixed),'#')),'#')
else
mixed
end
) mixed
from V
where instr(translate(mixed,'0123456789','9999999999'),'9') ＞ 0
9.4. PostgreSQL
9.4.1. sql
select cast(
case
when
replace(translate(mixed,'0123456789','9999999999'),'9','')
is not null
then
replace(
translate(mixed,
replace(
translate(mixed,'0123456789','9999999999'),'9',''),
rpad('#',length(mixed),'#')),'#','')
else
mixed
end as integer ) as mixed
from V
where 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 MIXED1
from (
select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c
from V,
( select id pos from t10 ) iter
where iter.pos ＜= length(v.mixed)
and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57
) y
group by mixed
order by 1
10. 提取第n個分隔子字元串
10.1. sql
create view V as
select 'mo,larry,curly' as name
from t1
union all
select 'tina,gina,jaunita,regina,leena' as name
from t1
10.2. sql
SUB
-----
larry
gina
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 name
order by length(substr(name,pos)) desc) rn
from (
select ',' ||csv.name|| ',' as name,
cast(iter.pos as integer) as pos
from V csv,
(select row_number() over() pos from t100 ) iter
where iter.pos ＜= length(csv.name)+2
) x
where length(substr(name,pos)) ＞ 1
and substr(substr(name,pos),1,1) = ','
) y
where rn = 2
10.4. Oracle
10.4.1. sql
select sub
from (
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) sub
from (select ','||name||',' as name from V) src,
(select rownum pos from emp) iter
where iter.pos ＜ length(src.name)-length(replace(src.name,','))
)
where pos = 2
10.5. PostgreSQL
10.5.1. sql
select name
from (
select iter.pos, split_part(src.name,',',iter.pos) as name
from (select id as pos from t10) iter,
(select cast(name as text) as name from v) src
where iter.pos ＜=
length(src.name)-length(replace(src.name,',',''))+1
) x
where pos = 2
10.6. MySQL
10.6.1. sql
select name
from (
select iter.pos,
substring_index(
substring_index(src.name,',',iter.pos),',',-1) name
from V src,
(select id pos from t10) iter,
where iter.pos ＜=
length(src.name)-length(replace(src.name,',',''))
) x
where 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 name
order by len(substring(name,pos,len(name))) desc) rn
from (
select ',' + csv.name + ',' as name,
iter.pos
from V csv,
(select id as pos from t100 ) iter
where iter.pos ＜= len(csv.name)+2
) x
where len(substring(name,pos,len(name))) ＞ 1
and substring(substring(name,pos,len(name)),1,1) = ','
) y
where 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 all
select 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) d
from (
select pos,c,d,
case when posstr(d,'.') ＞ 0 then substr(d,1,posstr(d,'.')-1)
else d
end as e,
row_number() over(order by pos desc) rn
from (
select pos, ip,right(ip,pos) as c, substr(right(ip,pos),2) as d
from x
where 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 ) d
from (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 d
from (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) d
from (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 t1
union all
select 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) d
from (
select pos,c,d,
case when charindex('.',d) ＞ 0
then substring(d,1,charindex('.',d)-1)
else d
end as e,
row_number() over(order by pos desc) rn
from (
select pos, ip,right(ip,pos) as c,
substring(right(ip,pos),2,len(ip)) as d
from x
where pos ＜= len(ip)
and substring(right(ip,pos),1,1) = '.'
) x
) y
