5.1生成连续数值

日常工作中常需要使用连续值来处理问题，我们可以使用任意表或VIEW来提取：

还可以通过树形查询的子句来提取：

5.2遍历字符串

有时会要求把字符串拆分为单个字符，如：

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

使用5.1节的技巧就可以把1行改为4行：

然后通过函数substr(汉字,level,?)得到需要的结果：

为了方便理解，我们同时显示了LEVEL的值及每一行实际执行的substr语句。

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

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

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

为了方便引用，首先建立一个VIEW：

Oracle 11g给出了新函数REGEXP_COUNT，我们可以直接引用：

若没有REGEXP_COUNT的版本怎么办？我们用REGEXP_REPLACE迂回求值即可：

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

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

错误的写法：

正确的写法：

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

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

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

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

这里面先把元音字母替换成‘a’，然后把‘a’去掉。

其实用前面介绍的TRANSLATE的一个用法就可以，根本不需要嵌套：

是不是要方便得多？

当然，也可以用更简便的正则函数REGEXP_REPLACE，直接把[]内列举的字符替换为空：

正则表达式regexp_replace与replace对应，regexp_replace(ename,'[AEIOU]')相当于同时执行了多个replace()函数：

5.5将字符和数字数据分离

建立测试用表如下：

从上面可知，dname中只有字母，而deptno中只有数字，你能从data中还原出dname与deptno吗？答案是肯定的，可以使用如下正则表达式：

我们前面讲过regexp_replace(data,'[0-9]','')就是多次的replace，[0-9]是一种表示方式，代表[0123456789]，还可以表示为[[:digit:]]。那么把这些数据替换之后剩下的就是那些字母了，得到的结果就是ename。

第二个表达式regexp_replace(data,'[^0-9]','')中多了一个符号"^"，这个符号表示否定的意思，代表[0-9]的外集，也就是除了[0123456789]外的所有字符，在本节案例中就是那些字母。把字母都替换掉之后，剩下的就是sal了。

要注意"^"的位置：在方括号内，所有的字符之前。

如果不是在方括号内（如直接写为'^hell'），则表示字符串的开始位置。

如果还不习惯使用正则表达式，则可以使用第二章介绍的translate:

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

示例数据如下：

上述语句中，有些数据包含了空格、逗号、$等字符。现在要求返回其中只有字母及数据的行（见粗体部分）。

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

首先和前面的对应方式一样，regexp_like 对应普通的like。

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

注意：是前后都有"%"的模糊查询。

我们知道，"^"不在方括号里时表示字符串开始，这里还有一个"$"，该符号在方括号外面，表示字符串的结束。

我们通过具体查询来对比说明：

用regexp_like对应普通的like来对比如下所示。

1.regexp_like(data,'A') 对应普通的like '%A%'。

2.前面加"^" regexp_like(data,'^A')对应普通的like 'A%'，没有了前模糊查询。

3.后面加"$" regexp_like(data,'A$')对应普通的like '%A'，没有了后模糊查询。

4.前后面加"^$" regexp_like(data,'^A$')对应普通的like 'A'，变成了精确查询。

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

我们用另一个例子来清理这几个关系。

regexp_like(str,'16+')加号前的子表达式是“６” ，至少匹配6一次，也就相当于 (like '16%' or like '166%' or ..)，等价于like '16%'。

regexp_like(str,'16*')加号前的子表达式也是“６” ，至少匹配6零次，也就相当于 (like '1%' or like '16%' or ..)，等价于like '1%'。

所以这两个条件的结果分别为：

那么当"+" "*" "^$"组合之后呢？我们再来看一个例子：

看了刚刚讲述的“+”“*”的区别，那么下面这两句结果有没有区别呢？

可能很多人都认为这两句的结果应该不一样，我们来运行一下：

是否有些意外？我们来看两个表达式对应的like应该是什么。

regexp_like(s,'^[12]+$')对应的是：

(s LIKE '1' OR s LIKE '2' OR s LIKE '11' OR s LIKE '22' OR s LIKE '12' OR s LIKE '21')

而regexp_like(s,'^[12]*$')对应的是：

(s LIKE '1' OR s LIKE '2' OR s LIKE '11' OR s LIKE '22' OR s LIKE '12' OR s LIKE '21' OR s LIKE '')

因为"*"可以匹配零次，所以多了一个条件OR s LIKE ''，但是我们在前面讲过，在这种条件里，空字符串等价于NULL，而NULL是不能用LIKE来比较的，所以这个条件不会返回值。

那么最终结果就是这两个语句返回的结果一样。

5.7提取姓名的大写首字母缩写

本例要求返回下面VIEW中的大写字母，中间加"."，显示为"M.H"：

我们可以利用regexp_replace的分组替换功能：

括号()将子表达式分组为一个替换单元、量词单元或后向引用单元。

在这个查询中，我们用（）把对应的字符串分成了四组，各组说明如下。

取第一组（＼１）与第三组（＼３），中间加上“．”就是我们需要的结果。

我们可以更改数据，包含多个人名，中间用逗号分隔：

因为第四组正则不匹配逗号，所以该语句实际是把上面的每个人名都按四组来处理。

5.8根据表中的行创建一个分隔列表

本例要求将emp表中的ename用逗号间隔合并在一起显示。如：CLARK,KING,MILLER。

可能很多人已使用过wmsys.wm_concat函数，但wmsys.wm_concat是一个非公开函数，具有不确定性（10G中返回类型是varchar，11.2中返回类型是clob）。从Oracle11.2开始就有了分析函数listagg。为了便于理解，下面将他与普通函数做一个类比：

如上结果所示，同sum一样，listagg在这里起汇总的作用。sum数值结果加在一起，而listagg是把字符串结果连在一起。

5.9提取第n个分隔的子串

首先建立如下视图：

上面各行中的字符串用逗号分隔，现要求将其中的第二个子串larry与gina取出来。

没有正则表达式之前需要找到逗号的对应位置，然后对字符串进行截取：

如果上面的语句不易理解，那么与下面个字符的位置对比一下就清楚了。

而用正则函数regexp_substr就要简单得多:

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

参数３:１表示从第一个字符开始。

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

5.10 分解IP地址

本例要求把IP地址"192.168.1.118"中的各段取出来，用前面学到的方法，参数４分别取１、２、３、４即可：

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

5.11 将分隔数据转换为多值IN列表

假设前端传入了一个字符串列表（如：CLARK,KING,MILLER），要求根据这个串查询数据：

直接把‘CLARK,KING,MILLER’代入肯定是查询不到数据的。

我们需要做转换。这是正则表达式的优势。

为了便于调用，我们先建一个视图：

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

为了便于理解，我们多显示了伪列level，及每行对应的正则表达式 [^,]+，表示对应一个不包含逗号的字符串，最后一个阐述表示分别取第1、2、3三个串。

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

5.12组合去重

我们有时会看到对组合进行去重的需求，如下面数据：

牛肉与土豆的组合有两条，而我们只需要一条，我们用上节的技巧来处理。

1.拆分出各成员

2.按顺序合并成员

3.处理全表数据

可以看到C2已是整理后的数据，可以愉快地去重了。