正則表達式一 (REGEXP方法) (DML 十)

官網連結
\：有四種意思
   1.代表本身
   2.引用下一個字符(跳脫字元)
   3.引入一個運算符
   4.沒做什麼
*：0~n
+：1~n
?：0~1
|：or
^：開頭
$：結尾
.：匹配任何字元，除了null
[]：[xyz]表示x或y或z，如xayaza，用a隔開還是可以匹配到，
    要注意在裡面使用「^」就不是開頭的意思了，是相反的意思
()：分組表達式，作為一個單獨的子表達式處理
{m}：m次
{m,}：至少m次
{m, n}：至少匹配m次，但不超過n次
\n：n為1~9的數字，用「()」包起來是第1個
[..]：指定一個對照元件，可以是一個多字串元件，如[.ch.]是西班牙語
[::]：POSIX語法，如[:alpha:]，下一篇會介紹
[==]：匹配相同的字元，如[=abc=]，裡面的字元，其中之一就匹配

\d：數字
\D：非數字
\w：字元
\W：非字元
\s：空格
\S：非空格
\A：只匹配字串的開頭，或前一個字串後面的換行字元
\Z：只匹配結尾的字串
*?：匹配前面的模式元素0次或更多次（非貪婪）
+?：匹配前面的模式元素1次或更多次（非貪婪）
??：匹配前面的模式元素0次或1次（非貪婪）
{m}?：匹配前面的模式元素m次（非貪婪）
{m,}?：匹配前面的模式元素至少m次（非貪婪）
{m,n}?：匹配前面的模式元素至少m次，但不超過n次（非貪婪）

※REGEXP_COUNT

正則表達式匹配的次數，官網連結

總共有4個參數，前兩個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：從第幾個index開始匹配
第4個參數一定是小寫，有5種形式
i：不區分大小寫
c：區分大小寫，預設
n：換行符號可以使用「.」來匹配
m：遇到換行符時，可以使用「^」和「$」來匹配
x：忽略空格，指的是正則表達式的空格(第2個參數)
也可以不放任何字，就等同沒有第四個參數，以上可以搭配使用

※i、c、x

SELECT
    REGEXP_COUNT('abc abc ab cabc', 'abc') C1,-- 3
    REGEXP_COUNT('abc abc ab cabc', 'AbC') C2,-- 0
    REGEXP_COUNT('abc abc ab cabc', 'abc', 3) C3,-- 2
    REGEXP_COUNT('abc abc ab cabc', 'AbC', 3, 'c') C4,-- 0
    REGEXP_COUNT('abc abc ab cabc', 'AbC', 3, 'i') C5,-- 2
    REGEXP_COUNT('abc abc ab cabc', 'a b c', 3, 'x') C6,-- 2
    REGEXP_COUNT('abc abc ab cabc', 'A b C', 3, 'xi') C7-- 2
FROM DUAL;

※說明：
C1可以匹配3次
C2一次都沒有，因為預設有區分大小寫
C3從第3個字元開始讀取(包括c)，所以是2次
C4其實就是預設的區分大小寫，所以一次都沒有
C5從第3個字元開始讀取(包括c)，不分大小寫，所以是2次
C6從第3個字元開始讀取(包括c)，而且忽略空格，所以是2次
C7從第3個字元開始讀取(包括c)，而且忽略空格，又不區分大小寫，所以是2次

※n和m

SELECT
    REGEXP_COUNT('123 ' || chr(10) || '67 ' || chr(10), '.', 1, 'i') N1,-- 7
    REGEXP_COUNT('123 ' || chr(10) || '67 ' || chr(10), '.', 1, 'in') N2,-- 9
    REGEXP_COUNT('aac' || chr(10) || 'abcaca' || chr(10) || 'abc', '^a', 1, '') M1,-- 1
    REGEXP_COUNT('aac' || chr(10) || 'abcaca' || chr(10) || 'abc', '^a', 1, 'm') M2-- 3
FROM DUAL;

※說明：
N1還沒有加「n」，所以匹配到的是「123空67空」，所以是匹配7次
N2有加「n」，所以會多2次
N3還沒有加「m」，所以預設算一行，匹配a開頭的，只有1次，最後的參數是空，所以也可以不要第4個參數
N4有加「m」，所以有3行，而且剛好都是a開頭的，所以是3次

※REGEXP_INSTR

正則表達式匹配後回傳第幾個index，沒有匹配到就回傳0，官網連結

總共有7個參數，前兩個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：從第幾個index開始匹配
第4個參數：匹配可能會有多筆，這個數字表示匹配第幾次成功的
第5個參數：不是0就是1，大於1就是1的意思，如果是0就回傳當下的index
如果是1，回傳當下的index之後，也就是再加1
第6個參數：和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT
第7個參數：巢狀匹配，不好說，直接看下面的第二個例子

※前6個參數

SELECT
    REGEXP_INSTR('123.56.89.BC', '\.') I1,-- 4
    REGEXP_INSTR('123.56.89.BC', '\.', 5) I2,-- 7
    REGEXP_INSTR('123.56.89.BC', '\.', 1, 3) I3,-- 10
    REGEXP_INSTR('123.56.89.BC', 'B', 1, 1, 0) I4,-- 11
    REGEXP_INSTR('123.56.89.BC', 'B', 1, 1, 1) I5,-- 12
    REGEXP_INSTR('123.56.89.BC', 'b', 1, 1, 0, 'i') I6-- 11
FROM DUAL;

※說明：
I1要匹配的是「.」，但它是關鍵字，所以用跳脫字元「\」
I2從第5個字元開始匹配，所後是5
I3從第1個字元開始匹配，但要回傳第三次成功的結果，所以是10
I4要比對「B」，而第5個參數是0，所以回傳當下的11
I5第5個參數是1，所以還要再加1，回傳12
I6的第6個參數不分大小寫

※第7個參數

SELECT
    REGEXP_INSTR('1234567890', '1234567', 1, 1, 0, 'i', 0) S1,-- 1
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) S2,-- 1
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) S3,-- 4
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) S4,-- 5
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) S5-- 7
FROM DUAL;

※說明：
 S1最後1個參數給0，匹配的到，其他不行

巢狀
(123)(4(56)(78))的順序如下：
一.123
二.45678
三.56
四.78
這樣就可以知道S2~S5的結果了，第5次以上，以這個例子就回傳0了

※REGEXP_REPLACE

取代正則表達式匹配成功的字串，沒有匹配到就回傳原來的字串，官網連結

總共有6個參數，前三個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：要取代的字串
第4個參數：從第幾個index開始匹配
第5個參數：匹配可能會有多筆，這個數字表示匹配第幾次成功的
第6個參數：和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT

※前3個參數

SELECT
    REGEXP_REPLACE('123     456    789,   abc   de,   FG', '( ){2,}', ' ') R1,
    REGEXP_REPLACE('123     456    789,   abc   de,   FG', '\s+', ' ') R2,
    REGEXP_REPLACE('ABC', '.', '\1 ') R3,-- \1 \1 \1 
    REGEXP_REPLACE('ABC', '(.)', '\1 ') R4,-- A B C
    REGEXP_REPLACE('A123 B456 C789', '(.*) (.*) (.*)', '\3, \2, \1') R5 -- C789, B456, A123
FROM DUAL;

※R1和R2都是匹配空格兩次以上的(包括2次)，取代成一個空格

※R3匹配任何字元，取代成\1空格

※R4因為有「()」，所以\1代表第1個()裡的正則表達式，而A、B、C都匹配成功，所以會將A、B、C後面多一個空格，看R5會比較容易了解

※R5會將第3個C789取代第1個A123；第2個還是第2個；第1個A123取代第三個C789

※第4~6個參數，，請參考上面的REGEXP_INSTR

※REGEXP_SUBSTR

從字串裡面取出子字串，如果都沒有匹配，回傳null，官網連結

總共有6個參數，前兩個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：從第幾個index開始匹配
第4個參數：匹配可能會有多筆，這個數字表示匹配第幾次成功的
第5個參數：和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT
第6個參數：巢狀匹配，請參考上面的REGEXP_INSTR

SELECT
    REGEXP_SUBSTR('12,345,678', ',[^,]+') S1,-- ,345
    REGEXP_SUBSTR('abc123def456', '[0-9]{1,}', 1, 2) S2,-- 456
    REGEXP_SUBSTR('aabab cde','^a.*b') S3-- aabab
FROM DUAL;

※S1是匹配以「,」開頭一直到非逗號的，所以是「,345」

※S2是匹配一次以上數字開頭的(\d無效)，從第1個index開始找，找到2次的結果，所以是456

※S3是匹配a開頭，然後停在b

※第4~6個參數，請參考上面的REGEXP_INSTR

※REGEXP_LIKE

使用在判斷條件時，通常是在WHERE後，官網連結

SELECT * FROM emp
WHERE REGEXP_LIKE (ename, 'N$');

※只要是N結尾的就匹配

※還有第三個參數，和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT