5.1生成連續(xù)數(shù)值

日常工作中常需要使用連續(xù)值來處理問題，我們可以使用任意表或VIEW來提?。?/p>

還可以通過樹形查詢的子句來提取：

5.2遍歷字符串

有時(shí)會(huì)要求把字符串拆分為單個(gè)字符，如：

為了核對(duì)表中保存的“首拼”是否正確，需要把字符串拆分為下面的樣式：

使用5.1節(jié)的技巧就可以把1行改為4行：

然后通過函數(shù)substr(漢字,level,?)得到需要的結(jié)果：

為了方便理解，我們同時(shí)顯示了LEVEL的值及每一行實(shí)際執(zhí)行的substr語句。

5.3計(jì)算字符在字符串中出現(xiàn)的次數(shù)

字符串'CLARK,KING,MILLER'被逗號(hào)分隔成了三個(gè)子串，現(xiàn)要求用SQL計(jì)算其中的子串個(gè)數(shù)，對(duì)于這種問題，我們一般計(jì)算其中的逗號(hào)個(gè)數(shù)后加1就可以了。

下面來看怎么計(jì)算逗號(hào)的個(gè)數(shù)。

為了方便引用，首先建立一個(gè)VIEW：

Oracle 11g給出了新函數(shù)REGEXP_COUNT，我們可以直接引用：

若沒有REGEXP_COUNT的版本怎么辦？我們用REGEXP_REPLACE迂回求值即可：

還可以使用前面介紹的translate:

如果分隔符有一個(gè)以上，那就要把計(jì)算出來的長(zhǎng)度在除以分隔符長(zhǎng)度。

錯(cuò)誤的寫法：

正確的寫法：

用regexp_count就可以不用考慮長(zhǎng)度：

可能有人注意到，第二個(gè)參數(shù)里多了一個(gè)“＼”。這是因?yàn)椤?”是通配符，需要用“＼”轉(zhuǎn)義。

5.4從字符串中刪除不需要的字符

若員工姓名中有元音字母（AEIOU），現(xiàn)在要求把這些元音字母都去掉，很多人都用如下語句：

這里面先把元音字母替換成‘a(chǎn)’，然后把‘a(chǎn)’去掉。

其實(shí)用前面介紹的TRANSLATE的一個(gè)用法就可以，根本不需要嵌套：

是不是要方便得多？

當(dāng)然，也可以用更簡(jiǎn)便的正則函數(shù)REGEXP_REPLACE，直接把[]內(nèi)列舉的字符替換為空：

正則表達(dá)式regexp_replace與replace對(duì)應(yīng)，regexp_replace(ename,'[AEIOU]')相當(dāng)于同時(shí)執(zhí)行了多個(gè)replace()函數(shù)：

5.5將字符和數(shù)字?jǐn)?shù)據(jù)分離

建立測(cè)試用表如下：

從上面可知，dname中只有字母，而deptno中只有數(shù)字，你能從data中還原出dname與deptno嗎？答案是肯定的，可以使用如下正則表達(dá)式：

我們前面講過regexp_replace(data,'[0-9]','')就是多次的replace，[0-9]是一種表示方式，代表[0123456789]，還可以表示為[[:digit:]]。那么把這些數(shù)據(jù)替換之后剩下的就是那些字母了，得到的結(jié)果就是ename。

第二個(gè)表達(dá)式regexp_replace(data,'[^0-9]','')中多了一個(gè)符號(hào)"^"，這個(gè)符號(hào)表示否定的意思，代表[0-9]的外集，也就是除了[0123456789]外的所有字符，在本節(jié)案例中就是那些字母。把字母都替換掉之后，剩下的就是sal了。

要注意"^"的位置：在方括號(hào)內(nèi)，所有的字符之前。

如果不是在方括號(hào)內(nèi)（如直接寫為'^hell'），則表示字符串的開始位置。

如果還不習(xí)慣使用正則表達(dá)式，則可以使用第二章介紹的translate:

5.6查詢只包含字母或數(shù)字型的數(shù)據(jù)

示例數(shù)據(jù)如下：

上述語句中，有些數(shù)據(jù)包含了空格、逗號(hào)、$等字符?，F(xiàn)在要求返回其中只有字母及數(shù)據(jù)的行（見粗體部分）。

如果直接按需求字面意思來寫，可以用正則表達(dá)式。

首先和前面的對(duì)應(yīng)方式一樣，regexp_like 對(duì)應(yīng)普通的like。

regexp_like(data,'[ABC]')就相當(dāng)于(like '%A%' or like '%B%' or like '%C%')；而regexp_like(date,'[0-9a-zA-Z]+')就相當(dāng)于(like '%數(shù)字%' or like '%小寫字母%' or like '%大寫字母%')。

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

我們知道，"^"不在方括號(hào)里時(shí)表示字符串開始，這里還有一個(gè)"$"，該符號(hào)在方括號(hào)外面，表示字符串的結(jié)束。

我們通過具體查詢來對(duì)比說明：

用regexp_like對(duì)應(yīng)普通的like來對(duì)比如下所示。

1.regexp_like(data,'A') 對(duì)應(yīng)普通的like '%A%'。

2.前面加"^" regexp_like(data,'^A')對(duì)應(yīng)普通的like 'A%'，沒有了前模糊查詢。

3.后面加"$" regexp_like(data,'A$')對(duì)應(yīng)普通的like '%A'，沒有了后模糊查詢。

4.前后面加"^$" regexp_like(data,'^A$')對(duì)應(yīng)普通的like?'A'，變成了精確查詢。

另一個(gè)概念是“+”與“*”。 '+'表示匹配前面的子表達(dá)式一次或多次；'*'表示匹配前面的子表達(dá)式零次或多次。

我們用另一個(gè)例子來清理這幾個(gè)關(guān)系。

regexp_like(str,'16+')加號(hào)前的子表達(dá)式是“６”?，至少匹配6一次，也就相當(dāng)于?(like '16%' or like '166%' or ..)，等價(jià)于like '16%'。

regexp_like(str,'16*')加號(hào)前的子表達(dá)式也是“６”?，至少匹配6零次，也就相當(dāng)于?(like '1%' or like '16%' or ..)，等價(jià)于like?'1%'。

所以這兩個(gè)條件的結(jié)果分別為：

那么當(dāng)"+" "*" "^$"組合之后呢？我們?cè)賮砜匆粋€(gè)例子：

看了剛剛講述的“+”“*”的區(qū)別，那么下面這兩句結(jié)果有沒有區(qū)別呢？

可能很多人都認(rèn)為這兩句的結(jié)果應(yīng)該不一樣，我們來運(yùn)行一下：

是否有些意外？我們來看兩個(gè)表達(dá)式對(duì)應(yīng)的like應(yīng)該是什么。

regexp_like(s,'^[12]+$')對(duì)應(yīng)的是：

(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]*$')對(duì)應(yīng)的是：

(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 '')

因?yàn)?#34;*"可以匹配零次，所以多了一個(gè)條件OR s LIKE ''，但是我們?cè)谇懊嬷v過，在這種條件里，空字符串等價(jià)于NULL，而NULL是不能用LIKE來比較的，所以這個(gè)條件不會(huì)返回值。

那么最終結(jié)果就是這兩個(gè)語句返回的結(jié)果一樣。

5.7提取姓名的大寫首字母縮寫

本例要求返回下面VIEW中的大寫字母，中間加"."，顯示為"M.H"：

我們可以利用regexp_replace的分組替換功能：

括號(hào)()將子表達(dá)式分組為一個(gè)替換單元、量詞單元或后向引用單元。

在這個(gè)查詢中，我們用（）把對(duì)應(yīng)的字符串分成了四組，各組說明如下。

取第一組（＼１）與第三組（＼３），中間加上“．”就是我們需要的結(jié)果。

我們可以更改數(shù)據(jù)，包含多個(gè)人名，中間用逗號(hào)分隔：

因?yàn)榈谒慕M正則不匹配逗號(hào)，所以該語句實(shí)際是把上面的每個(gè)人名都按四組來處理。

5.8根據(jù)表中的行創(chuàng)建一個(gè)分隔列表

本例要求將emp表中的ename用逗號(hào)間隔合并在一起顯示。如：CLARK,KING,MILLER。

可能很多人已使用過wmsys.wm_concat函數(shù)，但wmsys.wm_concat是一個(gè)非公開函數(shù)，具有不確定性（10G中返回類型是varchar，11.2中返回類型是clob）。從Oracle11.2開始就有了分析函數(shù)listagg。為了便于理解，下面將他與普通函數(shù)做一個(gè)類比：

如上結(jié)果所示，同sum一樣，listagg在這里起匯總的作用。sum數(shù)值結(jié)果加在一起，而listagg是把字符串結(jié)果連在一起。

5.9提取第n個(gè)分隔的子串

首先建立如下視圖：

上面各行中的字符串用逗號(hào)分隔，現(xiàn)要求將其中的第二個(gè)子串larry與gina取出來。

沒有正則表達(dá)式之前需要找到逗號(hào)的對(duì)應(yīng)位置，然后對(duì)字符串進(jìn)行截取：

如果上面的語句不易理解，那么與下面?zhèn)€字符的位置對(duì)比一下就清楚了。

而用正則函數(shù)regexp_substr就要簡(jiǎn)單得多:

參數(shù)2： "^"在方括號(hào)里表示否的意思，+表示匹配1次以上，'[^,]+'表示匹配不包含逗號(hào)的多個(gè)字符，也就是本節(jié)VIEW中的各個(gè)子串。

參數(shù)３:１表示從第一個(gè)字符開始。

參數(shù)４:２表示第二個(gè)能匹配'[^,]+'的字符串，也就是KING與FORD。

5.10 分解IP地址

本例要求把IP地址"192.168.1.118"中的各段取出來，用前面學(xué)到的方法，參數(shù)４分別?。?、２、３、４即可：

這是拆分字符常用的語句。

5.11 將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表

假設(shè)前端傳入了一個(gè)字符串列表（如：CLARK,KING,MILLER），要求根據(jù)這個(gè)串查詢數(shù)據(jù)：

直接把‘CLARK,KING,MILLER’代入肯定是查詢不到數(shù)據(jù)的。

我們需要做轉(zhuǎn)換。這是正則表達(dá)式的優(yōu)勢(shì)。

為了便于調(diào)用，我們先建一個(gè)視圖：

結(jié)合前面所講的知識(shí)，正則表達(dá)式如下：

為了便于理解，我們多顯示了偽列l(wèi)evel，及每行對(duì)應(yīng)的正則表達(dá)式?[^,]+，表示對(duì)應(yīng)一個(gè)不包含逗號(hào)的字符串，最后一個(gè)闡述表示分別取第1、2、3三個(gè)串。

那么結(jié)合這個(gè)語句就可以達(dá)到本例的需求。

5.12組合去重

我們有時(shí)會(huì)看到對(duì)組合進(jìn)行去重的需求，如下面數(shù)據(jù)：

牛肉與土豆的組合有兩條，而我們只需要一條，我們用上節(jié)的技巧來處理。

1.拆分出各成員

2.按順序合并成員

3.處理全表數(shù)據(jù)

可以看到C2已是整理后的數(shù)據(jù)，可以愉快地去重了。