【sql查询与优化】5.使用字符串

注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。

1.遍历字符串
有时候要求把字符串拆分成单个字符，如：

create or replace view v asselect '天天向上' as 汉字, 'TTXS' as 首拼 from dual;

为了核对表中保存的“首拼”是否正确，需要把字符串拆分成下面的样式:

汉字       首拼
————   ————
  天          T
  天          T  
  向          X
  上          S

拆分之前，我们先看一个connect by子句:

select level from dual connect by level<=4;

level
--------
  1
  2
  3
  4

其中，connect by是树形查询中的一个子句，后面level是一个“伪列”，表示树形查询中的级别层次，通过level<=4循环4次，就生成了4行上面所示的数据。

那么我们就可以通过connect by子句把v循环显示四行，并给出定位标识level：
select v.汉字,v.首拼,level from v connect by level <= length(v.汉字);

汉字                     首拼          LEVEL
------------------------ -------- ----------
天天向上                 TTXS              1
天天向上                 TTXS              2
天天向上                 TTXS              3
天天向上                 TTXS              4

根据上面的数据，就可以通过函数substr(v.汉字,level,?)得到需要的结果：

select v.汉字,        v.首拼,       level,       substr(v.汉字,level,1) as 汉字拆分,       substr(v.首拼,level,1) as 首拼拆分,       'substr(''' || v.汉字 || ''',' || level || ',1)' as fun    from v    connect by level<=length(v.汉字);

汉字                     首拼          LEVEL 汉字拆分 首拼拆分  FUN
------------------------ -------- ---------- -------- --------  ----------
天天向上                 TTXS              1 天       T         substr('天天向上',1,1)

天天向上                 TTXS              2 天       T         substr('天天向上',2,1)

天天向上                 TTXS              3 向       X         substr('天天向上',3,1)

天天向上                 TTXS              4 上       S         substr('天天向上',4,1)

可以看到我们是利用level的值将字符串进行拆分的，最后的fun可以看出来。

注:
substr(string,start,length)
string - 指定的要截取的字符串。
start - 必需，规定在字符串的何处开始。正数 - 在字符串的指定位置开始，负数 - 在从字符串结尾的指定位置开始，0 - 在字符串中的第一个字符处开始。
length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。


2.字符串文字包含引号
常常有人写SQL时不知道在字符串内的单引号怎么写，其实只要把一个单引号换成两个单引号表示就可以。

select 'g''day mate' qmarks from dual union allselect 'beavers''teeth' from dual union allselect '''' from dual;

QMARKS
--------------------------
g'day mate
beavers'teeth
'
注:
(Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
可以在最后一个结果集中指定Order by子句改变排序方式。)

另外，Oracle 10g开始引入了q-quote特性，允许按照指定的规则，也就是Q或者q开头(如果是national character literals，则是N或n放在Q或q之前)，字符串前后使用界定符“'”，使用规则很简单。

(1)q-quote 界定符可以是除了TAB、空格、回车外的任何字节或多字节字符。
(2)界定符可以是[]、{}、<>、()，而且必须成对出现。

上面的例子用q-quote的写法就比较明确了。

select q'[g'day mate]' qmarks from dual union allselect q'[beavers' teeth]' from dual union allselect q'[']' from dual;

QMARKS
----------------------------
g'day mate
beavers' teeth
'

3.计算字符在字符串中出现的次数

字符串'CLARK,KING,MILLER'被逗号分隔成了三个子串，现要求用SQL计算其中的子串个数，对于这种问题，我们一般计算其中的逗号个数后加1就可以。

下面来看怎么计算逗号的个数。

为了方便引用，首先建立一个view:

create or replace view v asselect 'CLARK,KING,MILLER' as str from dual;

SQL> select str from v;

STR
----------------------------------
CLARK,KING,MILLER

Oracle 11g给出了新函数regexp_count，我们可以直接引用。
select regexp_count(str,',')+1 as cnt from v;

若没有regexp_count的版本怎么办？我们用regexp_replace迂回求值即可。
select length(regexp_replace(str,'[^,]'))+1 as cnt from v;

 CNT
----------
         3
注:regexp_replace是正则表达式函数，是使用正则表达式来替换str
参数：
第一个是输入的字符串
第二个是正则表达式
第三个是替换的字符


还可以使用前面介绍的translate：

select length(translate(str,',' || str,','))+1 as cnt from v;

 CNT
----------
         3

注:translate(string,from_str,to_str)
    执行时,translate依次检查string中的每个字符是否在from_str中存在，如果不存在，那么这个string中的字符直接返回，如果存在，translate会记下这个字符在from_str中的位置，然后用to_str的同样位置的字符代替string中的这个字符作业返回结果。

如果分隔符有一个以上，那就要把计算出来的长度再除以分隔符长度。

create or replace view v asselect '10$#CLARK$#MANAGER' as str from dual;

正确写法:

select length(translate(str,'$#' || str,'$#'))/length('$#')+1 as cnt from v;

 CNT
----------
         3

而用regexp_count就可以不用考虑长度：

regexp_count(str,"\$#")+1 as cnt from v;

可能有人注意到，第二个参数里多了一个“\”，这是因为“$”是通配符，需要用“\”转义。

4.从字符串中删除不需要的字符

create or replace view employes asselect 'CLARK' as ename from dual union allselect 'KING' as ename from dual union allselect 'MILLER' as ename from dual;

若员工姓名中有元字母(AEIOU)，现在要求把这些元音字母去掉，很多人都用如下语句：

select ename,       replace(translate(ename,'AEIOU','aaaaa'),'a','') stripped1   from employes;

ENAME        STRIPPED1
------------ ------------------------------------------------
CLARK        CLRK
KING         KNG
MILLER       MLLR

这里先把元音字母替换成'a'，然后把'a'去掉。
其实用前面介绍的translate的一个用法就可以，根本不需要嵌套:

select ename,translate(ename,'1AEIOU','1') stripped1from employes;

ENAME        STRIPPED1
------------ ------------------------------------------------
CLARK        CLRK
KING         KNG
MILLER       MLLR

是不是要方便的多？
当然，也可以用更简单的正则函数regexp_replace，直接把[]内列举的字符替换为空:

select ename,      regexp_replace(ename,'[AEIOU]') as stripped1    from employes;

ENAME        STRIPPED1
------------ ------------------------------------------------
CLARK        CLRK
KING         KNG
MILLER       MLLR




5.将字符和数字数据分离
建立测试用表如下:

select * from dept2;

DATA
--------------
管理部门3322
销售部门3321
后勤部门3320
金融部门3319

data其实是部门名称和部门编号的连接，现在希望将部门名称和部门编号分别分开，并命名为dname和deptno。
可以使用如下正则表达式:

select regexp_replace(data,'[0-9]','') dname,    regexp_replace(data,'[^0-9]','') deptnofrom dept2;

DNAME       DEPTNO
----------  ----------
管理部门    3322

销售部门    3321

后勤部门    3320

金融部门    3319

在上面出现的正则表达式中，[0-9]是一种表示方式，代表[0123456789]，还可以表示为[[:digit:]]。那么把这些数据替换之后剩下的就是那些字母了。而[^0-9]中的“^”表示否定的意思，代表[0-9]的外集，也就是除[0123456789]外的所有字符。要注意“^”的位置:在方括号内，所有的字符之前。

如果不习惯使用正则表达式，还可以使用translate

select translate(data,'a0123456789','a') dname,     translate(data,'0123456789' || data,'0123456789') deptnofrom dept2;

DNAME       DEPTNO
----------  ----------
管理部门    3322

销售部门    3321

后勤部门    3320

金融部门    3319

6.查询只包含字母或数字型的数据
示例数据如下:

create or replace view v asselect '123' as data from dual union allselect 'abc' from dual union allselect '123abc' from dual union allselect 'abc123' from dual union allselect 'a1b2c3' from dual union allselect 'a1b2c3#' from dual union allselect '3$' from dual union allselect 'a 2' from dual;

SQL> select * from v;

DATA
--------------
123
abc
123abc
abc123
a1b2c3
a1b2c3#
3$
a 2
上述语句中，有些数据包含了空格/逗号/$等字符。现在要求返回其中只有字母及数据的行。

如果直接按需求字面意思来写，可以用正则表达式。

SQL> select data from v where regexp_like(data,'^[0-9a-zA-Z]+$');

DATA
--------------
123
abc
123abc
abc123
a1b2c3

regexp_like 就相当于普通的like。
regexp_like(data,'[ABC]')就相当于(like '%A%'or like '%B%'or like '%C%');而regexp_like(data,'[0-9a-zA-Z]+')就相当于(like '%数字%'or like '%小写字母%'or like '%大写字母%')。

“^”不在括号里时表示字符串开始，而“$”表示字母串的结束。例如regexp_like(data,'A')代表普通的like '%A%'；而regexp_like(data,'^A')代表普通的like 'A%'，即没有了前模糊查询；regexp_like(data,'A$')代表普通的like '%A'，即没有了后模糊查询；而regexp_like(data,'^A$')代表普通的like 'A'，即精确查询。

另一个概念是“+”与“*”。“+”表示匹配前面的自表达式一次或多次；“*”表示匹配前面的子表达式零次或多次。


7.提取姓名的大写首字母缩写
本例要求返回下面view中的大写字母，中间加“.”，显示为“M.H”:

create or replace view v asselect 'Micheal Hartstein' as ai from dual;

SQL> select * from v;

AI
----------------------------------
Micheal Hartstein

我们可以利用regexp_replace的分组替换功能:
select regexp_replace(v.ai,'([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') as sx from v;

SX
------
M.H

括号()将子表达式分组为一个替换单元、量词单元或后向引用单元。
在这个查询中，我们用()把字符串分成了四个组，其中第1、3组中是大写字母，然后通过后向引用('\1.\3')就分别取到了两个组的大写字母，并在中间增加了字符“.”。

即是，将字符串拆分为(1)大写([[:upper:]]) (2)小写(.*) (3)大写([[:upper:]]) (4)小写(.*)四个部分，取其(1)(3)。

也可以使用前面介绍的translate函数。
第一步，先把字符串转换为小写，这样就得到了translate所需的参数2

select ai,lower(ai) as a2 from v;

AI                    A2
--------------------- ----------------------
Micheal Hartstein     micheal hartstein

第二步，把空格替换为“.”，小写字母(上面得到的a2)置空就是我们需要的数据。

select translate(ai,' '||a2,'.')as a3 from (select ai,lower(ai) as a2 from v);

A3
------
M.H

8.按字符串中的数值排序
首先建立如下view，要求按照其中的数字排序：

create or replace view v asselect 'ACCOUNTING 10 NEW YORK' as data from dual union allselect 'OPERTIONS 40 BOSTON' as data from dual union allselect 'RESEARCH 20 DALLAS' as data from dual union allselect 'SALES 30 NEW CHICAGO' as data from dual;

SQL> select * from v;

DATA
--------------------------------------------
ACCOUNTING 10 NEW YORK
OPERTIONS 40 BOSTON
RESEARCH 20 DALLAS
SALES 30 NEW CHICAGO

我们可以用正则表达式替换非数字字符，语句如下:

select data,to_number(regexp_replace(data,'[^0-9]','')) as deptno from v order by 2;

DATA                          DEPTNO
----------------------------- ----------
ACCOUNTING 10 NEW YORK              10
RESEARCH 20 DALLAS                  20
SALES 30 NEW CHICAGO                30
OPERTIONS 40 BOSTON                 40

也可以使用translate函数，直接替换掉非数字字符：

select data,    to_number(translate(data,'0123456789'||data,'0123456789')) as deptno from v order by 2;

DATA                          DEPTNO
----------------------------- ----------
ACCOUNTING 10 NEW YORK              10
RESEARCH 20 DALLAS                  20
SALES 30 NEW CHICAGO                30
OPERTIONS 40 BOSTON                 40

9.提取第n个分隔的子串
首先建立如下view，要求按照其中的数字排序：

create or replace view v asselect 'CLARK,KING,MILLER' as name from dual union allselect 'ADAMS,FORD,JONES,SCOTT,SMITH' as name from dual;

SQL> select * from v;

NAME
--------------------------------------
CLARK,KING,MILLER
ADAMS,FORD,JONES,SCOTT,SMITH

上面各行中的字符串用逗号分隔，现在要求将其中的子串中的KING与FORD取出来。
没有正则表达式之前需要找到逗号的对应位置，然后对字符串进行截取：

select name,    第二个逗号后的位置,    第三个逗号的位置,    长度, substr(name,第二个逗号后的位置,长度) as 子串  from (select name,          instr(src.name, ',', 1, 2) + 1 as 第二个逗号后的位置,          instr(src.name, ',', 1, (2+1)) as 第三个逗号的位置,          instr(src.name, ',', 1, (2+1)) - instr(src.name, ',', 1, 2) - 1 as 长度        from(select ',' || name || ',' as name from v)src   )x;

                                                        
NAME                            第二个逗号后的位置   第三个逗号的位置       长度    子串
------------------------------  ------------------   ------------------    -------  -------
,CLARK,KING,MILLER,                     8                   12                4      KING

,ADAMS,FORD,JONES,SCOTT,SMITH,          8                   12                4      FORD

注:instr(sourceString,destString,start,appearPosition).   
其中sourceString代表源字符串；
destString代表想从源字符串中查找的子串；
start代表查找的开始位置，该参数可选的，默认为1；
appearPosition代表想从源字符中查找出第几次出现的destString，该参数也是可选的，默认为1；

而用正则表达函数regexp_substr就要简单的多：

select regexp_substr(v.name,'[^,]+',1,2) as 子串 from v;

子串
-------
KING
FORD

参数2:“^”在方括号里表示否的意思，+表示匹配1次以上，'[^,]+'表示匹配不包含逗号的多个字符，也就是本节view中的各个子串。

参数3:1表示从第一个字符开始。

参数4:2表示第二个能匹配'[^,]+'的字符串，也就是KING与FORD。

10.分析IP地址
本例要求把IP地址“192.168.1.118”中的各段取出来，用9节学到的方法，参数4分别取1,2,3,4即可:

select regexp_substr(v.ip, '[^.]+', 1, 1) a,    regexp_substr(v.ip, '[^.]+', 1, 2) b,    regexp_substr(v.ip, '[^.]+', 1, 3) c,    regexp_substr(v.ip, '[^.]+', 1, 4) dfrom (select '192.168.1.118' as ip from dual) v;

A      B      C  D
------ ------ -- ------
192    168    1  118

这是分拆字符常用的语句。

11.将分隔数据转换为多值IN列表
假设前端传入了一个字符串列表(如:张三,李四,李磊磊)，要根据这个串查询数据:

select * from emp where ename in (...);

直接把张三,李四,李磊磊带入肯定是查不到数据的:

SQL> select * from emp where ename in ('张三,李四,李磊磊');

未选定行

我们需要做转换，这正是正则表达式的优势。
为了便于调用，我们先建立一个视图:

create or replace view v asselect '张三,李四,李磊磊' as emps from dual;

结合前面所讲的知识，正则表达式如下:

select regexp_substr(v.emps,'[^,]+',1,level) as name,level   from vconnect by level <= (length(translate(v.emps,','||v.emps,','))+1);

NAME        LEVEL
---------   --------
张三        1

李四        2

李磊磊      3


那么结合本句就可以达到本例的需求。

SQL> var v_emps varchar2(100);SQL> exec :v_emps := '张三,李四,李磊磊';

PL/SQL 过程已成功完成。

SQL> select * from emp where ename in     (         select regexp_substr(:v_emps,'[^,]+',1,level) as ename from dual         connect by level <= (length(translate(:v_emps,','||:v_emps,','))+1)     );

     EMPNO ENAME                JOB                       MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- --------------  ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14      5200                    20

      1111 李四                 销售                     3321 03-11月-15      3400        500         30

      1114 李磊磊               会计                     3319 22-12月-15      2500                    50

注:exec 是 execute 的缩写，功能是执行一个存储过程，或者是执行一个动态SQL。

转载请注明出处：http://blog.csdn.net/acmman/article/details/51473622