程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> SQL高級(一)--正則函數，sql--函數

SQL高級(一)--正則函數，sql--函數

編輯：Oracle教程

SQL高級(一)--正則函數，sql--函數

    我們在進行字符串比對和處理的時候；可能用得最多是like子句；其中%代表一個到多個字符；_代表一個字符。但是處理比較復雜字符串時；發現這遠遠達不到我們所需的要求。所以這種低端，不美觀的語句。當然我們也不拋棄它們；畢竟oracle都沒放棄他們。在Oracle10g後，提供了若干與正則表達式相關的函數和操作。用好這些函數，可以大大提升我們的處理字符串的能力和水平。

   10g版本有4個正則函數，regexp_like,regexp_insrt,regexp_replace,regexp_substr。
   11g版本添加了：regexp_count。

先看下這個表；一切變換都在此表；這表就是正則的心法。

元字符 意思例子 \

說明要匹配的字符是一個特殊字符、常量或者後者引用。（後引用重復上一次的匹配)

\n 匹配換行符
\\ 匹配 \
$ 匹配 (
$ 匹配 ) ^ 匹配字符串的開頭位置 ^a匹配arwen.但不匹配barwen. $ 匹配字符串的末尾位置 en$匹配arwen.但不匹配arwenb. * 匹配前面的字符0次或多次 a*rwen可以匹配rwen或aaarwen. + 匹配前面的字符1次或多次 a+rwen可以匹配arwen或aarwen.但不能匹配rwen. ? 匹配前面的字符0次或1次 a?rwen可以匹配arwen或rwen.但不能匹配aarwen. {n} 匹配前面的字符恰好是n次，其中n是整數 ar{2}wen可以匹配arrwen.但不能匹配arwen或arrrwen. {n,m}

匹配前面的字符至少是n次，最多是m次.如果寫成

{n,}表示最少匹配n次.沒有上限.

ar{1,2}wen可以匹配arwen,arrwen.但不匹配awen或arrrwen. . 點號,匹配除null,換行以外的任意單個字符 arw.n.可以匹配arwen,arwin.但不能匹配arween或arwn. (pattern) 括號中pattern是一個子正則表達式，匹配指定pattern模式的一個子表達式。其實括號就像一般語言表達式中的括號.有時多加些括號可增強可讀性.另外的用處見下面關於\n的描述. x|y 匹配“或” x|y可以匹配x或者y [abc] 可以匹配abc中的任何單個字符 hello[abc]可以匹配helloa,hellob,helloc [a-z] 可以匹配指定范圍內的任何單個字符 hell[a-z]可以匹配hello或者hellz [::] 指定一個字符類，可以匹配該類中的任何字符 [:alphanum:]可以匹配字符0-9、A-Z、a-z
[:alpha:]可以匹配字符A-Z、a-z
[:blank:]可以匹配空格或tab鍵
[:digit:]可以匹配數字0-9
[:graph:]可以匹配非空字符
[:lower:]可以匹配小寫字母a-z
[:print:]與[:graph:]類似，不同之處在於[:print:]包括空格字符
[:punct:]可以匹配標點符號.,""等等
[:space:]可以匹配所有的空字符
[:upper:]可以匹配大寫字母A-Z
[:xdigit:]可以匹配十六進制數字0-9、A-F、a-f \n 這是對前一次匹配命中的一個後引用，其中n是一個正整數 arw(en)\1可以匹配arwenen.注意\1前面必須是個加括號的子表達式.

先提供一個事例表regexp_li0924來供我們練習：

 CREATE TABLE regexp_li0924(
 text VARCHAR(20)
);
INSERT INTO regexp_li0924 VALUES ('123');
INSERT INTO regexp_li0924 VALUES ('123a');
INSERT INTO regexp_li0924 VALUES ('123123?');
INSERT INTO regexp_li0924 VALUES ('afefwef');
INSERT INTO regexp_li0924 VALUES ('?');
INSERT INTO regexp_li0924 VALUES ('AFWEWE');
INSERT INTO regexp_li0924 VALUES ('1_2_3');
INSERT INTO regexp_li0924 VALUES ('1fe_24563');

1. REGEXP_LIKE函數

   regexp_like(srcstr,pattern[,match_option]): 查看srcstr是否與pattern相匹配，返回布爾值；該函數還可以提供一個可選的參數match_option字符串說明默認的匹配選項。其中match_option所選如下：
   'c'   說明在進行匹配時區分大小寫（缺省值）；
   'i'   說明在進行匹配時不區分大小寫；
   'n'   (.)點號能表示所有單個字符,包括換行(俺還不知道什麼地方有用到換行.只知道sql裡面可以用chr(10)表示換行.
   'm'   字符串存在換行的時候當作多行處理.這樣$就可匹配每行的結尾.不然的話$只匹配字符串最後的位置.
   舉例說明：

--查詢裡面含有數字的
SELECT * FROM regexp_li0924 WHERE regexp_like(text,'[[:digit:]]');
--查詢裡面含有數？的
SELECT * FROM regexp_li0924 WHERE regexp_like(text,'\?'); 
--查詢含有'a'
SELECT * FROM regexp_li0924 WHERE regexp_like(text,'a'); 
--查詢'a'或者'A'
SELECT * FROM regexp_li0924 WHERE regexp_like(text,'a','i');  --可能大家會想到這種。其實正則是多變的。於是有了第二種方法。
SELECT * FROM regexp_li0924 WHERE regexp_like(text,'(a|A)');

2. REGEXP_INSTR函數

REGEXP_INSTR(srcstr,pattern[,start[,occurrence[,return_option[, match_option]]]])：這個函數參數就比較多了；是基本SQL函數INSTR的一種拓展。Instr函數的作用是返回一個整數，表示一個字符串中第一次出現匹配字符串的位置編號。
至於參數就參考INSTR函數
我們用事例來解釋：我基本沒用到過這個函數

select regexp_instr(text,'[^[:digit:]]'),text from regexp_li0924; --這樣也是一種查非數字的方法
--從字符串'f2345ef4a56'第2個字符開始，查找zip中第3個非數字字符的位置
select regexp_instr('f2345ef456','[^[:digit:]]',2,3) from dual  --返回7
--從字符串'f2345ef4a56'第2個字符開始，查找zip中第3個非數字字符的下2個字符位置
select regexp_instr('f2345ef456','[^[:digit:]]',2,2,1) from dual;

3. REGEXP_REPLACE函數

REGEXP_REPLACE(srcstr,pattern[,replace_string[,start[,occurrence[, match_option]]]]);這個函數也相當於replace函數的擴展。返回是替換後的字符串。
參數跟上面差不多

--截取類似於字符串123_1563中的123
SELECT regexp_replace('123_1563','(.*)_(.*)','\1') FROM dual;
SELECT regexp_replace('123_1563','_[[:digit:]]+','') FROM dual;
--把字符串'123456fewfwefe34165'替換為0的字符串
SELECT regexp_replace('123456fewfwefe34165','[^[:digit:]]','0') FROM dual;
--把姓與名互相替換位置(因為英語名跟中文名是不一樣的。)
SELECT regexp_replace('mei han','(.*) (.*)','\2 \1' ) FROM dual;

4. REGEXP_SUBSTR函數

REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])用於在x中查找pattern並返回.這個函數我比較喜歡.因為很好用.這個在字符串分割中很受用.可以查看我的--sql中的字符分割.

--把字符串MMS-2501-MO取其中的2501.當然regexp_replace就可以搞定了。但我還是喜歡這種
SELECT regexp_substr('MMS_2501_MO','[^_]+',1,2) FROM dual;

5.REGEXP_COUNT函數

REGEXP_COUNT(srcstr,pattern[,match_option])用於返回匹配到的個數.oracle11g才支持

--例如取某個字符串中含有多少個0.這個當然你可以數數；但是你回去數嗎？也當然可以不用正則函數；你用了這個函數，會不會有高大上
SELECT regexp_substr('12308045600460','0') FROM dual;

作者： li0924

時間： 2014-11-04

本文版權歸作者所有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接.

SQL中怎調用一個函數

可以為函數的參數指定一個字符串
比如
SELECT [dbo].[w_GetProdColor]('test')
也可以用字段作為參數，其實跟系統函數用法是一樣的
SELECT [dbo].[w_GetProdColor](列名)
FROM 表名

sql 實現正則表達式

沒有正則表達式LIKE

確定給定的字符串是否與指定的模式匹配。模式可以包含常規字符和通配符字符。模式匹配過程中，常規字符必須與字符串中指定的字符完全匹配。然而，可使用字符串的任意片段匹配通配符。與使用 = 和 != 字符串比較運算符相比，使用通配符可使 LIKE 運算符更加靈活。如果任何參數都不屬於字符串數據類型，Microsoft® SQL Server™ 會將其轉換成字符串數據類型（如果可能）。

語法
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

參數
match_expression 任何字符串數據類型的有效 SQL Server 表達式。
patternmatch_expression 中的搜索模式，可以包含下列有效 SQL Server 通配符

1 % 包含零個或更多字符的任意字符串
示例：WHERE title LIKE '%computer% ' 將查找處於書名任意位置的包含單詞 computer 的所有書名。

2 _（下劃線）任何單個字符
示例：WHERE au_fname LIKE '_ean ' 將查找以 ean 結尾的所有 4 個字母的名字（Dean、Sean 等）。

3 [] 指定范圍中的任何單個字符
示例：WHERE au_lname LIKE '[C-P]arsen ' 將查找以arsen 結尾且以介於 C 與 P 之間的任何單個字符開始的作者姓氏，例如，Carsen、Larsen、Karsen 等

4 [^] 不屬於指定范圍中的任何單個字符，與 [] 相反
示例：WHERE au_lname LIKE 'de[^l]% ' 將查找以 de 開始且其後的字母不為 l 的所有作者的姓氏。

一使用 like 的模式匹配：
在搜索Datetime類型時，建議使用like .
LIKE 支持 ASCII 模式匹配和 Unicode 模式匹配。當所有參數，包括 match_expression、pattern 和 escape_character（如果有）都是 ASCII 字符數據類型時，將執行 ASCII 模式匹配。如果其中任何參數屬於 Unicode 數據類型，則所有參數將被轉換為 Unicode 並執行 Unicode 模式匹配。當對 Unicode 數據（nchar 或 nvarchar 數據類型）使用 LIKE 時，尾隨空格是有意義的。但是對於非 Unicode 數據，尾隨空格沒有意義。Unicode LIKE 與 SQL-92 標准兼容。ASCII LIKE 與 SQL Server 的早期版本兼容

二使用 % 通配符

例如，此查詢將顯示數據庫中所......余下全文>>