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-KING

2.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 t1

4. 统计字符出现的次数4.1. 10,CLARK,MANAGER4.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             800ALLEN            1600WARD             1250JONES            2975MARTIN           1250BLAKE            2850CLARK            2450SCOTT            3000KING             5000TURNER           1500ADAMS            1100JAMES             950FORD             3000MILLER           1300

5.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 13

5.3. DB25.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. 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 emp

5.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 emp

6. 分离数字和字符数据6.1. sql

DATA---------------SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450SCOTT3000KING5000TURNER1500ADAMS1100JAMES950FORD3000MILLER1300

6.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           ) x

6.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        ) x

7. 按照子字符串排序7.1. sql

ENAME----------SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER

7.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 SALES

8.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--------------CL10ARKI10NGMI10LL73697566778878767902ALLENWARDMARTINBLAKETURNERJAMES

9.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') ＞ 0

9.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') ＞ 0

9.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') ＞ 0

9.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 1

10. 提取第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 t1

10.2. sql

SUB-----larry gina

10.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 = 2

10.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 = 2

10.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 = 2

10.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 = 2

10.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 = 2

11. 解析IP地址11.1. 111.22.3.411.2. sql

A     B     C     D----- ----- ----- ---111   22    3     4

11.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        ) y

11.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 y

11.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) y

11.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