四大正則函數對照
| 函數 | 對應傳統函數 | 用途 |
|---|
REGEXP_LIKE | LIKE | 模式匹配條件判斷 |
REGEXP_INSTR | INSTR | 回傳匹配位置 |
REGEXP_SUBSTR | SUBSTR | 擷取匹配子字串 |
REGEXP_REPLACE | REPLACE | 替換匹配內容 |
語法結構與傳統函數相同，差別在於模式採用 POSIX 正則表達式，取代 % 與 _ 通配符。
常用 POSIX 元字符速查
| 元字符 | 說明 |
|---|
^ | 字串開頭 (多行模式下也匹配行首) |
$ | 字串結尾 (多行模式下也匹配行尾) |
. | 任意單一字元 |
? | 前一表達式 0 或 1 次 |
* | 前一表達式 0 或多次 |
+ | 前一表達式 1 或多次 |
{m,n} | 前一表達式出現 m 到 n 次 |
[...] | 字元集合 |
[^...] | 排除字元集合 |
| | 或 (替代) |
(...) | 分組/子表達式 |
\n | 反向參考第 n 個分組 |
POSIX 字元類別
| 類別 | 相當於 |
|---|
[[:ALPHA:]] | 字母 |
[[:DIGIT:]] | 數字 [0-9] |
[[:ALNUM:]] | 字母數字 |
[[:SPACE:]] | 空白字元 |
[[:UPPER:]] | 大寫字母 |
[[:LOWER:]] | 小寫字母 |
[[:PUNCT:]] | 標點符號 |
[[:XDIGIT:]] | 十六進位 [0-9A-Fa-f] |
實戰範例：建立測試資料
CREATE TABLE FZQ (ID VARCHAR(4), VALUE VARCHAR(10));
INSERT INTO FZQ VALUES ('1','1234560');
INSERT INTO FZQ VALUES ('2','1234560');
INSERT INTO FZQ VALUES ('3','1B3B560');
INSERT INTO FZQ VALUES ('4','ABC');
INSERT INTO FZQ VALUES ('5','ABCDE');
INSERT INTO FZQ VALUES ('6','ADREASX');
INSERT INTO FZQ VALUES ('7','123 45');
INSERT INTO FZQ VALUES ('8','ADC DE');
INSERT INTO FZQ VALUES ('9','ADC,.DE');
INSERT INTO FZQ VALUES ('10','1B');
INSERT INTO FZQ VALUES ('10','ABCBVBNB');
INSERT INTO FZQ VALUES ('11','11114560');
INSERT INTO FZQ VALUES ('11','11124560');
REGEXP_LIKE 實戰查詢
| 需求 | 傳統 LIKE | REGEXP_LIKE |
|---|
| 以 1 開頭、60 結尾、長度 7 | LIKE '1____60' | REGEXP_LIKE(VALUE, '1....60') |
| 同上且中間 4 碼皆為數字 | 難處理 | REGEXP_LIKE(VALUE, '1[0-9]{4}60') 或 REGEXP_LIKE(VALUE, '1[[:DIGIT:]]{4}60') |
| 非純數字記錄 | 無法簡單表達 | NOT REGEXP_LIKE(VALUE, '^[[:DIGIT:]]+$') |
| 不包含任何數字 | 無法簡單表達 | REGEXP_LIKE(VALUE, '^[^[:DIGIT:]]+$') |
| 以 12 或 1B 開頭 (不分大小寫) | 複雜 | REGEXP_LIKE(VALUE, '^1[2B]', 'i') |
| 以 12 或 1B 開頭 (分大小寫) | 複雜 | REGEXP_LIKE(VALUE, '^1[2B]') |
| 包含空白字元 | LIKE '% %' | REGEXP_LIKE(VALUE, '[[:SPACE:]]') |
| 純字母或純數字 | 無法表達 | REGEXP_LIKE(VALUE, '^([[:ALPHA:]]+|[[:DIGIT:]]+)$') |
| 包含標點符號 | 無法簡單表達 | REGEXP_LIKE(VALUE, '[[:PUNCT:]]') |
實務應用：姓名驗證函數
CREATE OR REPLACE FUNCTION CHECK_NAME (NAMESTR IN VARCHAR2) RETURN INTEGER AS
BEGIN
-- 空值或長度 < 2
IF NAMESTR IS NULL OR LENGTH(NAMESTR) < 2 THEN RETURN 0; END IF;
-- 包含「未取名」
IF NAMESTR LIKE '%未取名%' THEN RETURN 0; END IF;
-- 僅含字母或數字 (無中文)
IF REGEXP_LIKE(NAMESTR, '^([[:ALPHA:]]+|[[:DIGIT:]]+)$') THEN RETURN 0; END IF;
RETURN 1;
END CHECK_NAME;
總結
Oracle 正則函數將複雜字串驗證、擷取、替換邏輯從 PL/SQL 迴圈中解脫，轉為宣告式單行 SQL。熟練 POSIX 語法與字元類別，能大幅簡化資料清洗、驗證、轉換開發工作量。
