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) iterwhere iter.pos ＜= length(e.ename)C-KING2.3.T10表，该表有10行记录（它只有一列，列名为ID，它的值分别是从1到103.嵌入引号3.1.sql
QMARKS--------------g'day matebeavers' teeth'3.2.sql
select 'g''day mate' qmarks from t1 union allselect 'beavers'' teeth' from t1 union allselect '''' from t14.统计字符出现的次数4.1.10,CLARK,MANAGER4.1.1.该字符串里有多少个逗号4.2.sql
select (length('10,CLARK,MANAGER')- length(replace('10,CLARK,MANAGER',',','')))/length(',') as cnt from t14.3.获取不含逗号的字符串长度4.4.逗号的删除则借助了REPLACE函数5.删除不想要的字符5.1.sql
ENAME SAL---------- ----------SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500ADAMS 1100JAMES 950FORD 3000MILLER 13005.2.sql
ENAME STRIPPED1 SAL STRIPPED2--------- ---------- ---------- ---------SMITH SMTH 800 8ALLEN LLN 1600 16WARD WRD 1250 125JONES JNS 2975 2975MARTIN MRTN 1250 125BLAKE BLK 2850 285CLARK CLRK 2450 245SCOTT SCTT 3000 3KING KNG 5000 5TURNER TRNR 1500 15ADAMS DMS 1100 11JAMES JMS 950 95FORD FRD 3000 3MILLER MLLR 1300 135.3.DB25.3.1.sql
select ename, replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1, sal, replace(cast(sal as char(4)),'0','') stripped2 from emp5.4.Oracle5.5.PostgreSQL5.6.使用内置函数TRANSLATE和REPLACE删除不想要的字符和字符串5.6.1.sql
select ename, replace(translate(ename,'AEIOU','aaaaa'),'a') as stripped1, sal, replace(sal,0,'') as stripped2 from emp5.7.MySQL5.8.SQL Server5.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 emp6.分离数字和字符数据6.1.sql
DATA---------------SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450SCOTT3000KING5000TURNER1500ADAMS1100JAMES950FORD3000MILLER13006.2.DB26.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 ) x6.3.Oracle6.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.PostgreSQL6.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 ) x7.按照子字符串排序7.1.sql
ENAME----------SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER7.2.DB27.3.Oracle7.4.PostgreSQL7.5.MySQL7.6.使用内置函数LENGTH和SUBSTR7.6.1.sql
select ename from emp order by substr(ename,length(ename)-1,2)7.7.SQL Server7.7.1.sql
select ename from emp order by substring(ename,len(ename)-1,2)8.根据字符串里的数字排序8.1.sql
DATA-----------------------------CLARK 7782 ACCOUNTINGKING 7839 ACCOUNTINGMILLER 7934 ACCOUNTINGSMITH 7369 RESEARCHJONES 7566 RESEARCHSCOTT 7788 RESEARCHADAMS 7876 RESEARCHFORD 7902 RESEARCHALLEN 7499 SALESWARD 7521 SALESMARTIN 7654 SALESBLAKE 7698 SALESTURNER 7844 SALESJAMES 7900 SALES8.2.DB28.2.1.sql
select data from V order by cast( replace( translate(data,repeat('#',length(data)), replace( translate(data,'##########','0123456789'), '#','')),'#','') as integer)8.3.Oracle8.3.1.sql
select data from V order by to_number( replace( translate(data, replace( translate(data,'0123456789','##########'), '#'),rpad('#',20,'#')),'#'))8.4.PostgreSQL8.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--------------CL10ARKI10NGMI10LL73697566778878767902ALLENWARDMARTINBLAKETURNERJAMES9.2.DB29.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') ＞ 09.3.Oracle9.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') ＞ 09.4.PostgreSQL9.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') ＞ 09.5.MySQL9.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 110.提取第n个分隔子字符串10.1.sql
create view V asselect 'mo,larry,curly' as name from t1 union allselect 'tina,gina,jaunita,regina,leena' as name from t110.2.sql
SUB-----larry gina10.3.DB210.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 = 210.4.Oracle10.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 = 210.5.PostgreSQL10.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 = 210.6.MySQL10.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 = 210.7.SQL Server10.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 = 211.解析IP地址11.1.111.22.3.411.2.sql
A B C D----- ----- ----- ---111 22 3 411.3.DB211.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 ) y11.4.Oracle11.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.PostgreSQL11.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 y11.6.MySQL11.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) y11.7.SQL Server11.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