前言
本篇文章讲解的主要内容是：遍历拆分字符串为单个字符、字符串中包含引号如何转译(q-quote特性)、计算字符在字符串中出现的次数、使用translate从字符串中快速删除替换不需要字符的巧妙写法、使用正则表达式regexp_replace将字符和数字数据分离、使用正则表达式regexp_like查询只包含数字或字母型的数据
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、遍历拆分字符串为单个字符
有下面一个临时表，aa列是字符串，bb列是字符串中每个中文的首拼：
with t as (
select '我有一个做大牛的梦' as aa,'wyygzdndm'as bb from dual
)有时我们会有这么一个需求：要求把字符串拆分为单个字符，核对表中保存的“首拼”是否正确。
如果要实现这个需求，需要把两个字段的字符串拆分一个个核对！！！那么怎么通过SQL来实现这个需求？
在实现这个需求前先看一个CONNECT BY子句：
SQL> SELECT LEVEL FROM dual CONNECT BY LEVEL<= 4 ;
LEVEL
----------
1
2
3
4
其中，CONNECT BY是树形查询中的一个子句，后面的LEVEL是一个“伪列“，表示树形中的级别层次，通过LEVEL<=4循环4次，就生成了4行上面所示的数据。
那么我们就可以通过CONNECTBY子句把T表数据循环显示4行，并给出定位标识LEVEL:
SQL> with t as (
2 select '我有一个做大牛的梦' as aa,'wyygzdndm'as bb from dual
3 )
4 select t.aa,t.bb,level from t connect by level<=4;
AA BB LEVEL
-------------------------------- -------------------------------- ----------
我有一个做大牛的梦 wyygzdndm 1
我有一个做大牛的梦 wyygzdndm 2
我有一个做大牛的梦 wyygzdndm 3
我有一个做大牛的梦 wyygzdndm 4根据上面的数据，就可以通过函数substr(汉字，level,？)得到需要的结果：
SQL> with t as (
2 select '我有一个做大牛的梦' as aa,'wyygzdndm'as bb from dual
3 )
4 select t.aa,t.bb,level,substr(t.aa,level,1) as 汉字拆分,substr(t.bb,level,1) as 字母拆分 from t connect by level<=length(t.aa);
AA BB LEVEL 汉字拆分 字母拆分
-------------------------------- -------------------------------- ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
我有一个做大牛的梦 wyygzdndm 1 我 w
我有一个做大牛的梦 wyygzdndm 2 有 y
我有一个做大牛的梦 wyygzdndm 3 一 y
我有一个做大牛的梦 wyygzdndm 4 个 g
我有一个做大牛的梦 wyygzdndm 5 做 z
我有一个做大牛的梦 wyygzdndm 6 大 d
我有一个做大牛的梦 wyygzdndm 7 牛 n
我有一个做大牛的梦 wyygzdndm 8 的 d
我有一个做大牛的梦 wyygzdndm 9 梦 m
9 rows selected为了方便理解，我们同时显示了LEVEL的值及每一行实际执行的substr语句。
二、字符串中包含引号如何转译
平时写SQL时我们经常会遇到一个字符串中含有很多个引号这种情况，那么如何转译呢？其实只要把一个单引号换成两个单引号表示就可以。
SQL> select 'zyd''zhaoyd' from dual;
'ZYD''ZHAOYD'
--------------------------------
zyd'zhaoyd另外，Oracle10g开始引入了q-quote特性，允许按照指定的规则，也就是Q或q开头
（如果是national character literals,则是N或n放在Q或q之前），字符串前后使用界定符"'"'使用规则很简单：q-quote界定符可以是除了TAB、空格、回车外的任何单字节或多字节字符。
界定符可以是[]、{}、<>、()而且必须成对出现。q-quote的写法就比较明确了。
SQL> select q'[zyd'z'h''ao'yd]' from dual;
'ZYD''Z''H''''AO''YD'
--------------------------------
zyd'z'h''ao'yd三、计算字符在字符串中出现的次数
现在有这么一个临时表：
with t as (
select 'zyd,zhaoyd,zhao,yan,dong' as aa from dual
)字符串'zyd,zhaoyd,zhao,yan,dong'内部每个词汇被逗号分隔开来。
现在有这么一个要求：计算其中单词个数！
对这种问题，我们一般计算其中的逗号个数后加1就可以。
Oracle11g给出了新函数REGEXP_COUNT,我们可以直接用来计算逗号的个数。
with t as (
select 'zyd,zhaoyd,zhao,yan,dong' as aa from dual
)
select regexp_count(aa,',')+1 from t;
REGEXP_COUNT(AA,',')+1
----------------------
5若没有REGEXP_COUNT的版本怎么办？我们用REGEXP_REPLACE迂回求值即可：
with t as (
select 'zyd,zhaoyd,zhao,yan,dong' as aa from dual
)
select length(regexp_replace(aa,'[^,]+'))+1 as cnt from t;
CNT
----------
5如果你不会用正则的话，还可以用前面介绍的translate:
with t as (
select 'zyd,zhaoyd,zhao,yan,dong' as aa from dual
)
select translate(aa,','||aa,',') as tran,length(translate(aa,','||aa,','))+1 as cnt from t;
TRAN CNT
---------------------------------------------------------------- ----------
,,,, 5如果分隔符有一个以上，而且是成对出现的，那就要把计算出来的长度再除以分隔符长度，比如下面这个测试数据统计。
with t as (
select 'zyd$#zhaoyd$#zhao$#yan$#dong' as aa from dual
)
select translate(aa,'$#'||aa,'$#') as tran,(length(translate(aa,'$#'||aa,'$#'))/length('$#'))+1 as cnt from t;
TRAN CNT
---------------------------------------------------------------- ----------
$#$#$#$# 5切记，一定不要写成下面这样，因为字符成对出现的，最后还要除一下再+1
with t as (
select 'zyd$#zhaoyd$#zhao$#yan$#dong' as aa from dual
)
select translate(aa,'$#'||aa,'$#') as tran,length(translate(aa,'$#'||aa,'$#'))/length('$#')+1 as cnt from t;当然了，用REGEXP_COUNT就可以不用考虑长度：
with t as (
select 'zyd$#zhaoyd$#zhao$#yan$#dong' as aa from dual
)
select regexp_count(aa,'\$#') +1as cnt from t;
CNT
----------
5第二个参数里多了一个"\"。这是因为"$"是通配符，代表以..字符结尾的意思，需要用\转义。
四、使用translate从字符串中快速删除替换不需要字符的巧妙写法
如果我们想快速替换掉dept表中dname列所有原因字母，很多人会使用REPLACE(translate(dname,'AEIOU','aaaaa'),'a','')这种方式：
SQL> select dname,REPLACE(translate(dname,'AEIOU','aaaaa'),'a','') as str from dept;
DNAME STR
-------------- --------------------------------------------------------------------------------
ACCOUNTING CCNTNG
RESEARCH RSRCH
SALES SLS
OPERATIONS PRTNS其实不用这么麻烦去嵌套，我们可以这么写：
SQL> select dname,translate(dname,'1AEIOU','1') as str from dept;
DNAME STR
-------------- ----------------------------
ACCOUNTING CCNTNG
RESEARCH RSRCH
SALES SLS
OPERATIONS PRTNS这种方式是不是很方便？
五、使用正则表达式regexp_replace将字符和数字数据分离
有这么一个测试表：
with t as (
select ename||empno from emp
)
select * from t;现在有一个需求：将字母全都删掉还原出empno为新的字段
那么我们可以这么写
with t as (
select ename||empno aa from emp
)
select regexp_replace(aa,'[^[:digit:]]+') a,regexp_replace(aa,'[^0-9]+') b from t;
A B
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
7369 7369
7499 7499
7521 7521
7566 7566
7654 7654
7698 7698
7782 7782
7788 7788
7839 7839
7844 7844
7876 7876
7900 7900
7902 7902
7934 7934
1001 1001
15 rows selected上面[^[:digit:]]、[^0-9]两种写法是等价的，如果你不会用正则表达式，那么还是可以使用translate来实现！
with t as (
select ename||empno aa from emp
)
select regexp_replace(aa,'[^[:digit:]]+') a,regexp_replace(aa,'[^0-9]+') b,translate(aa,'0123456789'||aa,'0123456789')cc from t
A B CC
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
7369 7369 7369
7499 7499 7499
7521 7521 7521
7566 7566 7566
7654 7654 7654
7698 7698 7698
7782 7782 7782
7788 7788 7788
7839 7839 7839
7844 7844 7844
7876 7876 7876
7900 7900 7900
7902 7902 7902
7934 7934 7934
1001 1001 1001
15 rows selected可以看到，cc列也是一样的结果~我这里是推荐大家学习一下常用的正则表达式！因为真的太方便了，博主工作中已经习惯使用正则来实现translate、repalce、count、like等等操作了，正则表达式后续实验案例中博主还会继续使用。
六、使用正则表达式regexp_like查询只包含数字或字母型的数据
有如下临时表：
with t as (
select '123' as aa from dual union all
select 'abc' as aa from dual union all
select '123abc' as aa from dual union all
select 'abcl23' as aa from dual union all
select 'a1b2c3' as aa from dual union all
select 'alb2c3#' as aa from dual union all
select '3$' as aa from dual union all
select 'a 2' as aa from dual
)上面表中，有些数据行包含了空格、逗号、$等字符。
现在有一个需求：返回只有字母及数据的行（像3$这种不要）。
直接按需求字面意思来写，可以用正则表达式。
with t as (
select '123' as aa from dual union all
select 'abc' as aa from dual union all
select '123abc' as aa from dual union all
select 'abcl23' as aa from dual union all
select 'a1b2c3' as aa from dual union all
select 'alb2c3#' as aa from dual union all
select '3$' as aa from dual union all
select 'a 2' as aa from dual
)
select * from t where regexp_like(aa,'^[0-9a-zA-Z]+$');
AA
-------
123
abc
123abc
abcl23
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对应普通的like来对比就是：
- 用
regexp_like(data,'A')对应普通的like '%A%'：
with t as (
select 'A' as aa from dual union all
select 'AB' as aa from dual union all
select 'BA' as aa from dual union all
select 'BAC'as aa from dual
)
select * from t where regexp_like(aa,'A');
AA
---
A
AB
BA
BAC- 前面加'
^'的regexp_like(aa,'^A')对应普通的like 'A%',没有了前模糊查询：
SQL> with t as (
2 select 'A' as aa from dual union all
3 select 'AB' as aa from dual union all
4 select 'BA' as aa from dual union all
5 select 'BAC'as aa from dual
6 )
7 select * from t where regexp_like(aa,'^A');
AA
---
A
AB- 后面加
'$'的regexp_like(aa,'A$')对应普通的like '%A',没有了后模糊查询：
SQL> with t as (
2 select 'A' as aa from dual union all
3 select 'AB' as aa from dual union all
4 select 'BA' as aa from dual union all
5 select 'BAC'as aa from dual
6 )
7 select * from t where regexp_like(aa,'A$');
AA
---
A
BA- 前后各加上
'^A$'的regexp_like(aa,'^A$')对应普通的like'A',变成了精确查询。
SQL> with t as (
2 select 'A' as aa from dual union all
3 select 'AB' as aa from dual union all
4 select 'BA' as aa from dual union all
5 select 'BAC'as aa from dual
6 )
7 select * from t where regexp_like(aa,'^A$');
AA
---
A另一个概念是'+'与'*'。'+'表示匹配前面的子表达式一次或多次：'*'表示匹配前面的子表达式零次或多次。
我们用另一个例子来理清这几个关系。
SQL> with t as (
2 select '167' as aa from dual union all
3 select '1667' as aa from dual union all
4 select '17' as aa from dual union all
5 select '1234567' as aa from dual
6 )
7 select * from t where regexp_like(aa,'6+')
8 ;
AA
-------
167
1667
1234567
SQL> with t as (
2 select '167' as aa from dual union all
3 select '1667' as aa from dual union all
4 select '17' as aa from dual union all
5 select '1234567' as aa from dual
6 )
7 select * from t where regexp_like(aa,'6*');
AA
-------
167
1667
17
1234567regexp_like(str,'6+')子表达式是'6+'至少匹配6一次，也就相当于(like'6%'or like'66%' or..),等价于like'6%'.regexp_like(str,'6*')子表达式是’6*‘,至少匹配6零次，也就相当于(like'*'or like'6%' or..),等价于like'*'。
那么当'+*^$'组合之后呢？先整一个临时表：
with t as (
select 1 as aa from dual union all
select 12 as aa from dual union all
select 13 as aa from dual union all
select 2 as aa from dual union all
select 21 as aa from dual union all
select 23 as aa from dual union all
select 3 as aa from dual union all
select 31 as aa from dual union all
select 32 as aa from dual union all
select null as aa from dual
)来看一下这俩查询有啥区别？
select * from t where regexp_like(aa,'^[12]+$');
select * from t where regexp_like(aa,'^[12]*$');可能很多人都认为这两句的结果应该不一样，我们来运行一下：
SQL> with t as (
2 select 1 as aa from dual union all
3 select 12 as aa from dual union all
4 select 13 as aa from dual union all
5 select 2 as aa from dual union all
6 select 21 as aa from dual union all
7 select 23 as aa from dual union all
8 select 3 as aa from dual union all
9 select 31 as aa from dual union all
10 select 32 as aa from dual union all
11 select null as aa from dual
12 )
13 select * from t where regexp_like(aa,'^[12]+$');
AA
----------
1
12
2
21
SQL> with t as (
2 select 1 as aa from dual union all
3 select 12 as aa from dual union all
4 select 13 as aa from dual union all
5 select 2 as aa from dual union all
6 select 21 as aa from dual union all
7 select 23 as aa from dual union all
8 select 3 as aa from dual union all
9 select 31 as aa from dual union all
10 select 32 as aa from dual union all
11 select null as aa from dual
12 )
13 select * from t where regexp_like(aa,'^[12]*$');
AA
----------
1
12
2
21呕吼，是否有些意外？
我们来看两个表达式对应的like应该是什么。regexp_like(aa,'^[12]+$')对应的是1,2,11,22,21,12
而regexp_like(aa,'^[12]*$')对应的是1,2,11,22,21,12,''
因为*可以匹配零次，所以多了一个条件OR aa LIKE"但我们在前面讲过，在这种条件里，空字符串等价NULL。而NULL是不能用LIKE来比较的，所以这个条件不会返回值。
SQL> with t as (
2 select 1 as aa from dual union all
3 select 12 as aa from dual union all
4 select 13 as aa from dual union all
5 select 2 as aa from dual union all
6 select 21 as aa from dual union all
7 select 23 as aa from dual union all
8 select 3 as aa from dual union all
9 select 31 as aa from dual union all
10 select 32 as aa from dual union all
11 select null as aa from dual
12 )
13 select count(*) from t where aa like '';
COUNT(*)
----------
0那么最终结果就是这两个语句返回的结果一样。
总结
这篇文章主要讲解的是常用的字符处理方法，不过还好，讲的内容都挺基础，后面还会有几篇文章继续讲这块，因为对数据的处理在工作中非常常见。
