Oracle 查询技巧与优化（三） 字符串操作_字符串每三位加逗号oracle

前言

前两篇blog分别介绍了Oracle中的单表查询（Oracle查询技巧与优化（一） 单表查询与排序）与多表查询（Oracle查询技巧与优化（二） 多表查询）的相关技巧与优化，那么接下来本篇blog就具体研究一下Oracle查询中使用频率很高的一种数据类型——“字符串”的相关操作与注意点。

常用操作符与函数

如题，先简单回顾一下我个人认为在Oracle的查询或存储过程中使用频率较高的操作符与函数，作为基础知识回顾，首先是最常用的字符串连接符“||”，即Oracle中的字符串拼接符号，例如：

select 'wlwlwlwl' || '015' || 'csdn' as title from dual;
1

运行结果如下：

再看几个简单的比较常用的字符串函数，首先是instr与substr，这两个函数经常结合使用，下面分别看一下，先来看看相对简单一些的substr函数，它的语法格式如下：SUBSTR(cExpression，nStartPosition [，nCharactersReturned])，通俗的讲，第1个参数是源字符串，第2个参数是开始截取的位置，第3个参数是截取长度，例如：

select substr('abcdefg',0,1) as newstr from dual; // 返回a
select substr('abcdefg',1,1) as newstr from dual; // 返回a，0和1都代表第1个字符
select substr('abcdefg',2,4) as newstr from dual; // 返回bcde，注意包含位置字符
select substr('abcdefg',-3,2) as newstr from dual; // 返回ef，负数表示从后往前数位置，其余不变
1
2
3
4

substr函数实在不需要做过多说明，接下来看看instr函数，它的作用是“在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置”，有点类似于java中String的indexOf方法：

select instr('abcd','a') from dual; // 返回1
select instr('abcd','b') from dual; // 返回2
select instr('abcd','c') from dual; // 返回3
select instr('abcd','e') from dual; // 返回0
1
2
3
4

如上所示，如果找不到指定的子串，则返回0，以上是instr函数最简单的用法，接下来具体看一下instr函数完整的语法格式：instr(string1,string2[,start_position[,nth_appearence]])，可以看到它最多支持4个参数，下面分别解释一下每个参数的意思：

1. string1，即源字符串。
2. string2，目标字符串，即要在string1中查找的字符串。
3. start_position，从string1开始查找的位置。可选，默认为1，正数时，从左到右检索，负数时，从右到左检索。
4. nth_appearence，查找第几次出现string2。可选，默认为1，不能为负。

如上所示，之所以instr函数很强大往往是依赖第4个参数的作用，在某些场合往往能起到关键作用，下面看一个我项目中的例子，首先学生表有一个字段记录了每个学生的体育考试选考科目，每人选5门待考科目，每个科目都有一个代码，在学生表存储的数据格式是“每门科目代码加逗号拼接的字符串”，形如：

如上图，每一个选考项在字典表均可对应：

现在假设我们有这样一个需求，把每个学生的每一门体育选考项目的代码和名称分别列出来，达到以下的效果：

如上图所示，该如何实现呢？首先思路很明确，把学生表记录的选考科目代码字符串进行拆分，然后再一一列举，那么此时需要注意的一个问题是代码的长度不确定，科目代码可能是1位数字，例如：1,2,3,8,9，但也可能存在两位数字，例如：1,2,3,11,12，那么直接用substr按位截取肯定是行不通的，此时我们应该想办法如何按符号截取，每个考试科目代码中间都是用逗号隔开的，如果能找到相邻两个逗号之间的数字，那么问题就迎刃而解了，这里需要用到的就是前面说的instr和substr相结合：

select t1.sid_,
       t1.stuname_,
       t1.km1,
       t2.itemvalue_ km1name,
       t1.km2,
       t3.itemvalue_ km2name,
       t1.km3,
       t4.itemvalue_ km3name,
       t1.km4,
       t5.itemvalue_ km4name,
       t1.km5,
       t6.itemvalue_ km5name
  from (select sid_,
               stuname_,
               substr(tysxkm_, 0, instr(tysxkm_, ',', 1, 1) - 1) as km1,
               substr(tysxkm_,
                      instr(tysxkm_, ',', 1, 1) + 1,
                      instr(tysxkm_, ',', 1, 2) - instr(tysxkm_, ',', 1, 1) - 1) as km2, 
               substr(tysxkm_,
                      instr(tysxkm_, ',', 1, 2) + 1,
                      instr(tysxkm_, ',', 1, 3) - instr(tysxkm_, ',', 1, 2) - 1) as km3,
               substr(tysxkm_,
                      instr(tysxkm_, ',', 1, 3) + 1,
                      instr(tysxkm_, ',', 1, 4) - instr(tysxkm_, ',', 1, 3) - 1) as km4,
               substr(tysxkm_, instr(tysxkm_, ',', 1, 4) + 1) as km5
          from t_studentinfo) t1
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t2
    on t1.km1 = t2.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t3
    on t1.km2 = t3.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t4
    on t1.km3 = t4.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t5
    on t1.km4 = t5.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t6
    on t1.km5 = t6.itemkey_;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

如上所示，第15行找到第1个逗号的位置，同时通过减1来算出截取长度，17-18行则是找到第2个和第3个逗号的位置，并截取出其中的考试科目代码，依此类推，而25行则是找到最后一个逗号的位置并直接截取后半段得到最后一项考试科目代码，这样就完成了每项考试科目代码的分割，核心思想是通过符号来分割，在blog后面将介绍一种更为简便的方式（正则函数REGEXP_SUBSTR），此处暂且用substr和instr组合的方式来实现，旨在回顾基础性的重点内容，接下来再看一些Oracle字符串相关的基础性常用的函数，比如求字符串长度：

select length('abcdefg') as length_ from dual; // 返回7
1

去空格函数trim：

select trim('   abcdefg   ') from dual; // 去左右空格
select ltrim('   abcdefg') from dual; // 去左空格
select rtrim('abcdefg   ') from dual; // 去右空格
1
2
3
4

还有字母大小写转换函数：

select upper('AbCdEfG') from dual; // 小写转大写 返回ABCDEFG
select lower('AbCdEfG') from dual; // 大写转小写 返回abcdefg
1
2
3

Oracle中基础性的常用函数先记录这么多，接下来具体研究一下Oracle中较为复杂的字符串应用场景以及相关函数。

字符串文字中包含引号

如题，单引号的转义问题，解决方法很简单，只需要把一个单引号换成两个单引号表示即可：

除此之外，在Oracle10g开始引入了q-quote特性，允许按照指定规则，也就是Q或q开头，字符串前后使用界定符”’”，规则很简单：

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

举例来看一下：

如上图所示，格式也很简单，不必再做过多解释。

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

如题，例如有如下字符串：GREEN,RED,YELLOW,BLUE,WHITE，现在需要用SQL查询出其中包含的单词个数，通常我们只需要算出字符串中的逗号个数再加1就行了，那么如何计算字符串中某个字符的个数呢？这里就要用到Oracle的正则表达式函数了：

select length(regexp_replace('GREEN,RED,YELLOW,BLUE,WHITE', --source_char，源字符串
                             '[^,]', --pattern，正则表达式（替换满足正则的值）
                             '', --replace_string，替换后的目标字符串
                             1, --position，起始位置（默认为1）
                             0, --occurrence，替换的次数（0是无限次）
                             'i')) --match_parameter，匹配行为（'i'是无视大小写）
       + 1 as count_
  from dual;
1
2
3
4
5
6
7
8
9

运行结果如下：

如上图所示，注释中简要说明了REGEXP_REPLACE函数每个参数的含义，通常只需要前三个参数即可，在Oracle的Database Online Documentation中可以看到该函数的格式：

再简单解释一下上面的SQL语句，可以发现正则表达式是[^,]，中括号中的^符号表示“不包含以及否定的意思”（而中括号外的^则表示字符串的开始，更多的Oracle正则表达式可参考Oracle正则表达式使用介绍），所以regexp_replace('GREEN,RED,YELLOW,BLUE,WHITE', '[^,]','')就表示将“除了逗号以外的所有字符替换成空”，然后通过length函数就算出了逗号的长度（个数），最后再加1就得到单词的个数了。从Oracle 11g开始又引入了一个新的正则函数REGEXP_COUNT使得这个问题的解决方案更加简单了：

select regexp_count('GREEN,RED,YELLOW,BLUE,WHITE', ',') + 1 as count_
  from dual;
1
2

运行结果如下：

如上图所示，REGEXP_COUNT函数可以直接计算出逗号的个数，所以就无需再通过REGEXP_REPLACE去迂回计算了，但注意这个函数是Oracle 11g之后引入的，既然提到了REGEXP_COUNT，下面就具体看一下它的语法格式：

如上图，可以看到REGEXP_COUNT相较于REGEXP_REPLACE简单一些，必选参数依然两个，用法也很简单，关于REGEXP_COUNT暂且先介绍这么多。

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

如题，这次以经典的scott.emp表为例：

比如我有这样一个需求，我想把ENAME这一列中的名字中包含的元音字母（AEIOU）去掉，那么该如何做呢？上一小节提到过REGEXP_REPLACE这个函数，显而易见，就用它就可以轻松的完成替换：

select regexp_replace(ename, '[AEIOU]') as ENAME from scott.emp;
1

运行结果如下：

如上图所示，通过REGEXP_REPLACE函数很容易将AEIOU这5个字母转换为空字符串，从而避免了直接用replace函数要进行多层嵌套的问题。

字符和数字相分离

如题，回到我们的学生表，首先创建一个测试用的视图：

create or replace view v_test_11 as
select bmh_ || stuname_ as data from t_studentinfo;

select * from v_test_11;
1
2
3
4

运行结果如下：

如上所示，我们通过拼接学生的考号和姓名组成了新的一列data，现在的需求是再将这一列按考号和姓名拆分开来，如何实现呢？很明显拆分的是数字和汉字，那么自然用正则最为合适了：

select regexp_replace(data, '[0-9]') as name1,
       regexp_replace(data, '[^0-9]') as zkzh
  from v_test_11;
1
2
3

运行结果如下：

如上图，可以看到成功拆分了考号和姓名，简单解释一下上面的正则，[0-9]代表数字[0123456789]，还可以表示为[[:digit:]]，那么将数字全部替换后自然就剩下了所有的汉字组成的NAME1字段，同理，[^0-9]表示[0-9]的外集，即“除了0123456789”之外的所有字符，那么将除了数字之外的所有字符替换为空，剩下的自然就是纯数字组成的ZKZH字段了，还需要注意一点就是^符号，它在方括号表达式内的意思是“否定、非、相反”的意思，如果它在方括号表达式以外则表示“字符串开始”的意思，这个会在后面的例子中再做说明。

按字符串中的数值排序

如题，依旧先创建一个测试视图：

create or replace view v_test_05 as
select stuname_ || ' ' ||schoolname_ || ' ' || lqfs_ as data from t_lq order by stuname_;

select * from v_test_05;
1
2
3
4
5

运行结果如下：

如上图，我们再一次进行了拼接构造数据，这次拼接了三列，分别是学生姓名，所在学校以及考试总分，我们这次的需求是按照分数倒序排列（目前是按照姓名拼音字母顺序排序的），该如何处理呢？通过这几个例子应该找出规律了，这种提取字符串中某一类型的数据再做相关操作的直接用正则表达式肯定是最方便的！所以我们依然通过正则提取列中的分数再来进行排序即可：

select data from v_test_05 order by to_number(regexp_replace(data, '[^0-9]')) desc
1

运行结果如下所示：

如上图，依旧通过正则[^0-9]将字符串中的非数字全部替换为空，然后得到纯分数字符串后再通过to_number函数转换为数字即可排序，与上面的几个例子思路基本一致，都应用了正则函数REGEXP_REPLACE，关于REGEXP_REPLACE的函数至此就介绍的差不多了，最后再补充一个例子是今年项目中遇到的一个问题，需求是“将查询出的字符串每个字符中间加空格后返回”，比如：数据库中的字段值原本是”abc”，那么查询出来应当是”a b c”，如果是”王小明”，那么查询出来应当是”王 小 明”，解决这个问题最简单的方式依然是通过正则：

select regexp_replace('abc','(.)','\1 ') as data from dual
1

运行结果如下图所示：

如上图所示，正则(.)表示匹配除换行符n之外的任意单个字符，而\1则是每个字符自身再加一个空格，所以就得到了我们预期的结果。

提取第n个分隔的子串

如题，这里就会用到我们blog开头用instr和substr写的那个例子的简便写法，上面也说了会用到将要介绍的第三个正则函数REGEXP_SUBSTR，所以先看看这个正则函数的语法格式以及参数说明：

如上图，看一下每个参数的含义：

1. source_string：源字符串
2. pattern：正则表达式
3. position：起始位置
4. occurrence：第n个能匹配正则的字符串

可以看到和前面的正则函数都差不多，接下来将blog开头的例子改写成REGEXP_SUBSTR的形式：

select t1.sid_,
       t1.stuname_,
       t1.km1,
       t2.itemvalue_ km1name,
       t1.km2,
       t3.itemvalue_ km2name,
       t1.km3,
       t4.itemvalue_ km3name,
       t1.km4,
       t5.itemvalue_ km4name,
       t1.km5,
       t6.itemvalue_ km5name
  from (select sid_,
               stuname_,
               regexp_substr(tysxkm_, '[^,]+', 1, 1) as km1,
               regexp_substr(tysxkm_, '[^,]+', 1, 2) as km2,
               regexp_substr(tysxkm_, '[^,]+', 1, 3) as km3,
               regexp_substr(tysxkm_, '[^,]+', 1, 4) as km4,
               regexp_substr(tysxkm_, '[^,]+', 1, 5) as km5
          from t_studentinfo) t1
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t2
    on t1.km1 = t2.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t3
    on t1.km2 = t3.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t4
    on t1.km3 = t4.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t5
    on t1.km4 = t5.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t6
    on t1.km5 = t6.itemkey_;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

运行结果如下：

如上图，可以看到通过REGEXP_SUBSTR来做“按符号截取字符串”要比instr和substr组合好用的多， 正则中加号表示匹配1次以上的意思，所以[^,]+所以表示匹配不包含逗号的多个字符，在此也就表示被逗号分隔后的各个子串。REGEXP_SUBSTR的第3个参数表示“从第1个字符开始”，同其它正则函数的position都是一样的，而重点是第4个参数，表示第n个能匹配到该正则的字符串，分隔之后自然就是按自然顺序就可以取到各字符串了。

分解IP地址

如题，例如需要将一个ip地址中的各段取出来，显而易见和上面的需求完全一样，只不过一个是逗号分隔，而ip地址是点分隔：

select regexp_substr(v.ipaddr, '[^.]+', 1, 1) as firstpart,
       regexp_substr(v.ipaddr, '[^.]+', 1, 2) as secondpart,
       regexp_substr(v.ipaddr, '[^.]+', 1, 3) as thirdpart,
       regexp_substr(v.ipaddr, '[^.]+', 1, 4) as fourthpart
  from (select '192.168.0.100' as ipaddr from dual) v
1
2
3
4
5

运行结果如下：

和上一个例子用法一模一样，在此就不做过多赘述了。

查询只包含字母或数字型的数据

如题，说到查询中的正则，那么肯定是要用到REGEXP_LIKE这个函数了，依旧是先看一下REGEXP_LIKE的语法格式和参数说明：

如上图所示，REGEXP_LIKE只有3个参数，用法类似于普通的LIKE模糊查询，下面依次看一下这每个参数的含义：

1. source_string：源字符串
2. pattern：正则表达式
3. match_parameter：匹配参数，例如’i’，作用是忽略大小写

REGEXP_LIKE返回匹配正则的字符，依旧通过例子来看，首先创建一个测试view：

create or replace view v_test_06 as
select '123' as data from dual union all
select 'abc' from dual union all
select '123abc' from dual union all
select 'abc123' from dual union all
select 'a1b2c3' from dual union all
select 'a1b2c3#' from dual union all
select '3$' from dual union all
select 'a 2' from dual union all
select '0123456789' from dual union all
select 'b3#45' from dual;
1
2
3
4
5
6
7
8
9
10
11
12

查询一下这个view：

如上所示，可以看到准备了一组测试数据，那么接下来的需求是“查询只包含字母或数字的值“，就是说只能包含字母和数字，不能有其它字符，很明显依然要通过正则来判断，下面先看一下完整的写法：

select data from  v_test_06 where regexp_like(data,'^[0-9A-Za-z]+$');
1

运行结果如下：

如上图，可以看到已经成功过滤出只包含字符和数字的值，那么接下来具体解释一下这个正则^[0-9A-Za-z]+$，首先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 ‘%大写字母%’。需要格外注意的一点就是^符号，它在中括号外面表示字符串开始（在前面的例子中均在字符串内，表示否定及相反），^[0-9A-Za-z]意思就是匹配以任意数字或者任意大小写字母开头的字符串，而正则中的$符号表示字符串结束，所以[0-9A-Za-z]$就表示匹配任意数字或大小写字母结尾的字符串，完全类似于模糊查询的LIKE ‘A%’ 和LIKE ‘%A’，但是^$在一起的时候的就是精确查找了，比如：

那么上面这个正则^[0-9A-Za-z]+$为何能匹配到所有“只包含字符或数字的值”呢？其实加号（+）在这里也起到了关键作用，加号在正则中的意思是“匹配前面的表达式一次或多次”，所以在这里加号就表示每一个字符都要匹配[0-9A-Za-z]这个规则且以数字字母开头和结尾，所以这样就查出了只包含字符或数字的值。

列转行

如题，最后的一个话题，谈谈Oracle11.2版本开始提供的一个用于列传行的函数listagg，用法和postgresql9.3中的string_agg函数基本一致，我在之前的博客也专门介绍过postgres的string_agg这个函数（postgresql 9.3 自定义聚合函数实现多行数据合并成一列），但语法上比postgres的string_agg函数更繁琐一些，首先来看一下listagg这个函数的语法格式：

如上图，listagg函数有4个参数，下面简单解释一下：

1. measure_expr：分组中每个列的表达式，也就是说需要合并的列
2. delimiter：分隔符，不设置的话，就表示无分割符
3. order_by_clause：进行合并中要遵守的排序顺序
4. query_partition_clause：表示listagg是具有分析函数analyze funcation特性

所以说listagg尽管更多被用作聚集函数，但它还是有analyze funcation特性。下面通过一个例子具体看一下listagg的用法，首先学生体育成绩表有如下数据：

如上图，是一张体育考试成绩表，每名学生有5门考试成绩，可以看到ZKZH_这一列是考号，SKXMDM_是之前说过的每门考试科目的代码，而HSCJ_就是考试成绩了，假设我现在的需求是按考号分组，将每名学生的SKXMDM_用逗号拼接，同时求体育总成绩，该怎么做呢？很典型的一个列传行，这里用list_agg函数就再合适不过了：

select zkzh_,
       listagg(skxmdm_, ',') within group(order by zkzh_) as skxmdm,
       sum(hscj_) score
  from T_SPORTTESTSCORE t
 where t.kz1_ = 1
 group by zkzh_
1
2
3
4
5
6

运行结果如下：

如上图所示，可以看到很好的完成了列的合并以及sum求和，到此为止Oracle中字符串相关的函数暂且介绍到这里，后面有机会还会陆续添加。

总结

本篇blog着重记录了Oracle中和字符串相关的一些个人认为比较常用及重要的函数和相关使用场景，重难点是那4个正则函数，如果在查询中能灵活运用正则函数的话确实能快捷的实现一些复杂的需求，最后由于个人能力有限难免有疏漏之处欢迎各位读者批评指正，同样也希望对读了本文的新手朋友们有所帮助，The End。