Oracle正規表示式函式：regexp_like、regexp_substr、regexp_instr、regexp_replace

Sql程式碼
Oracle使用正規表示式離不開這4個函式：

1。regexp_like

2。regexp_substr

3。regexp_instr

4。regexp_replace


看函式名稱大概就能猜到有什麼用了。

regexp_like 只能用於條件表示式，和 like 類似，但是使用的正規表示式進行匹配，語法很簡單：

regexp_substr 函式，和 substr 類似，用於拾取合符正規表示式描述的字元子串，語法如下：

regexp_instr 函式，和 instr 類似，用於標定符合正規表示式的字元子串的開始位置，語法如下：

regexp_replace 函式，和 replace 類似，用於替換符合正規表示式的字串，語法如下：

這裡解析一下幾個引數的含義：
1。source_char，輸入的字串，可以是列名或者字串常量、變數。

2。pattern，正規表示式。

3。match_parameter，匹配選項。取值範圍：
i：大小寫不敏感； c：大小寫敏感；n：點號 . 不匹配換行符號；m：多行模式；x：擴充套件模式，忽略正規表示式中的空白字元。
4。position，標識從第幾個字元開始正規表示式匹配。
5。occurrence，標識第幾個匹配組。
6。replace_string，替換的字串。


Sql程式碼
oracle 10g正規表示式 REGEXP_LIKE 用法


/*
ORACLE中的支援正規表示式的函式主要有下面四個：
1，REGEXP_LIKE ：與LIKE的功能相似
2，REGEXP_INSTR ：與INSTR的功能相似
3，REGEXP_SUBSTR ：與SUBSTR的功能相似
4，REGEXP_REPLACE ：與REPLACE的功能相似

它們在用法上與Oracle SQL 函式LIKE、INSTR、SUBSTR 和REPLACE 用法相同，
但是它們使用POSIX 正規表示式代替了老的百分號（%）和萬用字元（_）字元。
POSIX 正規表示式由標準的元字元（metacharacters）所構成：
'^' 匹配輸入字串的開始位置，在方括號表示式中使用，此時它表示不接受該字符集合。[^]
'$' 匹配輸入字串的結尾位置。如果設定了 RegExp 物件的 Multiline 屬性，則 $ 也匹
配 '\n' 或 '\r'。
'.' 匹配除換行符之外的任何單字元。
'?' 匹配前面的子表示式零次或一次。
'+' 匹配前面的子表示式一次或多次。
'*' 匹配前面的子表示式零次或多次。
'|' 指明兩項之間的一個選擇。例子'^([a-z]+|[0-9]+)$'表示所有小寫字母或數字組合成的
字串。

'( )' 標記一個子表示式的開始和結束位置。
'[]' 標記一箇中括號表示式。
'{m,n}' 一個精確地出現次數範圍，m= 出現m次。
\num 匹配 num，其中 num 是一個正整數。對所獲取的匹配的引用。
字元簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何數字。
[[:alnum:]] 任何字母和數字。
[[:space:]] 任何白字元。
[[:upper:]] 任何大寫字母。
[[:lower:]] 任何小寫字母。
[[:punct:]] 任何標點符號。
[[:xdigit:]] 任何16進位制的數字，相當於[0-9a-fA-F]。
各種運算子的運算優先順序
\轉義符
(), (?:), (?=), [] 圓括號和方括號
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和順序
|
*/


Sql程式碼
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL>create table fzq
(
id varchar(4),
value varchar(10)
);
表已建立。


----資料插入
SQL> insert into fzq values ('1','1234560');
已建立 1 行。

SQL> insert into fzq values ('2','1234560');

已建立 1 行。

SQL> insert into fzq values ('3','1b3b560');

已建立 1 行。

SQL> insert into fzq values ('4','abc');

已建立 1 行。

SQL> insert into fzq values ('5','abcde');

已建立 1 行。

SQL> insert into fzq values ('6','ADREasx');

已建立 1 行。

SQL> insert into fzq values ('7','123 45');

已建立 1 行。

SQL> insert into fzq values ('8','adc de');

已建立 1 行。

SQL> insert into fzq values ('9','adc,.de');

已建立 1 行。

SQL> insert into fzq values ('10','1B');

已建立 1 行。

SQL> insert into fzq values ('10','abcbvbnb');

已建立 1 行。

SQL> insert into fzq values ('11','11114560');

已建立 1 行。

SQL> insert into fzq values ('11','11124560');

已建立 1 行。

SQL> commit;

提交完成。


[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where value like '1____60';

ID VALUE
---- ----------
1 1234560
2 1234560
3 1b3b560

SQL> select * from fzq where regexp_like(value,'1....60');

ID VALUE
---- ----------
1 1234560
2 1234560
3 1b3b560
11 11114560
11 11124560

SQL> select * from fzq where regexp_like(value,'1[0-9]{4}60');

ID VALUE
---- ----------
1 1234560
2 1234560
11 11114560
11 11124560

SQL> select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');

ID VALUE
---- ----------
1 1234560
2 1234560
11 11114560
11 11124560





-- 查詢value中不是純數字的記錄
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where not regexp_like(value,'^[[:digit:]]+$');

ID VALUE
---- ----------
3 1b3b560
4 abc
5 abcde
6 ADREasx
7 123 45
8 adc de
9 adc,.de
10 1B
10 abcbvbnb

已選擇9行。


-- 查詢value中不包含任何數字的記錄。
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where regexp_like(value,'^[^[:digit:]]+$');

ID VALUE
---- ----------
4 abc
5 abcde
6 ADREasx
8 adc de
9 adc,.de
10 abcbvbnb

已選擇6行。






--查詢以12或者1b開頭的記錄.不區分大小寫。
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where regexp_like(value,'^1[2b]','i');
ID VALUE
---- ----------
1 1234560
2 1234560
3 1b3b560
7 123 45
10 1B





--查詢以12或者1b開頭的記錄.區分大小寫。
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where regexp_like(value,'^1[2B]');

ID VALUE
---- ----------
1 1234560
2 1234560
7 123 45
10 1B



-- 查詢資料中包含空白的記錄。
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where regexp_like(value,'[[:space:]]');

ID VALUE
---- ----------
7 123 45
8 adc de



--查詢所有包含小寫字母或者數字的記錄。
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');

ID VALUE
---- ----------
1 1234560
2 1234560
4 abc
5 abcde
10 abcbvbnb
11 11114560
11 11124560

已選擇7行。



--查詢任何包含標點符號的記錄。
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from fzq where regexp_like(value,'[[:punct:]]');

ID VALUE
---- ----------
9 adc,.de


SQL> select * from dual where regexp_like('b', '^[abc]$');


D
-
X




SQL> select * from dual where regexp_like('abc', '^[abc]$');

未選定行

SQL> select * from dual where regexp_like('abc', '^a[abc]c$');

D
-
X


regexp_substr拆分字串集合。
regexp_substr函式格式如下：
function regexp_substr(String, pattern, position, occurrence, modifier)
srcstr ：需要進行正則處理的字串
pattern ：進行匹配的正規表示式
position ：起始位置，從第幾個字元開始正規表示式匹配（預設為1）
occurrence ：標識第幾個匹配組，預設為1
modifier ：模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索。預設為'c'。）

eg:
[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
SQL> select * from t1;
A B C
-------------------- -------------------- --------------------------------------------------
A B a,a,b,c,d,s,c,b

SQL> select distinct a,b,regexp_substr(c,'[^,]+',1,level,'i') c from
2 t1
3 connect by level <= length(c) - length(replace(c,',','')) + 1;
A B C
-------------------- -------------------- --------------------------------------------------------------------------------
A B d
A B b
A B a
A B c
A B s

[sql] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
select * from test
where regexp_like(indphy, '([a-z]+|[A-Z])')--含有字

[sql] ---不去重複
SQL> select * from testaa;

ID NAME
---------- --------------------
3 a,a,b,c,d,s,c,b

SQL> SELECT id,REGEXP_SUBSTR(name, '[^,]+', 1, LEVEL) AS substr FROM testaa CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(name,'[^,]+'))+1;

ID SUBSTR
---------- --------------------
3 a
3 a
3 b
3 c
3 d
3 s
3 c
3 b

8 rows selected.

SQL>

Oracle正規表示式函式

相關文章