Oracle SQL 查询优化.Part5

一、字符串

1. 字符串截取：

语法：substr(string, start, length)，其中 string 是要截取的字符串，start 是开始的字符下标，length 是截取的长度。同时可以结合使用 instr 函数判断子串的位置，从字串第一次出现的位置开始截取。

-- 字符串截取
select substr(empdesc, instr(empdesc, '，') + 1, instr(empdesc, '。')) str from emp;

在使用 instr 的时候需要注意，如果子串不存在的话，instr 返回的是 0。

-- 如果子串不存在，返回 0
select instr(empdesc, 'a') index_a from emp;

2. 字符串中包含引号：

如果字符串中包含单引号，需要敲两次，字符串中两个单引号输出一个单引号：

-- 字符串中包含单引号
select 'Servyou''s Future!' str from dual;

除此之外，从 Oracle 10g 开始引入了 q-quote 特性：按照指定规则，字符串前后使用界定符 “ ' ” 。其中 q-quote 的写法规则是：a. q-quote 界定符可以是除了 Tab、空格、回车外任何单字符或者多字节字符；b. 界定符可以是 {}、[]、<>、()，但必须是成对出现。

-- q-quote 特性、界定符
select q'{Servyou's Future!}' t_group,
       q'[Dqy's Dream!]' t_dept,
       q'(Persons' benifit)' t_person
  from dual;

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

-- 计算字符在字符串中出现的次数
select regexp_count(q'[Servyou's future is Dqy's dream, it can bring persons' benifit]', q'[']') quote_count from dual;

4. regexp_replace —— 字符串替换

regexp_replace 正则功能强大，下边举例可以实现的一些需求：

• 删除不必要字符

-- 删除不必要字符
select regexp_replace('Hello Oracle SQL!', 'Oracle ') replace_res from dual;

• 将字符和数据分离

-- 将数字字符与字母分离
select regexp_replace(empno, '[0-9]', '') empno_char,
       regexp_replace(empno, '[^0-9]', '') empno_num
  from emp;

这里， ^ 字符放在括号里表示否定。但是如果  ^ 放在括号外边表示字符串开始，下边会遇到。当然这里也可以用前几个part 中讲的 translate 函数实现：

-- translate 实现
select translate(empno, 'a0123456789', 'a') emp_char,
       translate(empno, '0123456789' || empno, '0123456789') emp_num
  from emp;

5. regexp_like —— 正则 like

regexp_like 对应普通的like，用在 where 子句中。

-- 查询只包含字母数字型的数据
select empname from emp where regexp_like(empname, '^[0-9a-zA-Z]+$');

                     

这里，对正则用到的字符做一下说明：

^ : 字符串起；$ : 字符串终；+ : 表示匹配前边字符串 1 次或多次；* : 表示匹配字符串 0 次或多次。

再举几个例子：

regexp_like(str, '[A]')       相当于       (like '%A%')

regexp_like(str, '[ABC]')     相当于       (like '%A%' or like '%B%' or like '%C%')

regexp_like(str, '^A')        相当于       (like 'A%')

regexp_like(str, 'A$')        相当于       (like '%A')

regexp_like(str, '^A$')       相当于       (like 'A')

6. listagg —— 根据表中的行创建一个分隔列表

个人理解就是行合并，转为一个字段。其中 wmsys.wm_concat 也可以实现这样的需求，但是 wmsys.wm_concat 是一个非公开函数，具有不确定性，大多都不推荐使用。Oracle 11.2 版本开始有了 listagg 函数：

-- listagg 函数
select deptno,
       listagg(empno, ', ') within group (order by deptno) empno_str,
       listagg(empname, ', ') within group (order by deptno) empname_str
  from emp
group by deptno;