sql正則表達式

A. 如何在sql語句中使用正則表達式

sqlserver中，主要有regexp_like,regexp_replace,regexp_substr,regexp_instr四個正則表達式函數。

1、regexp_like:

regexp_like(x,pattern[,match_option]),查看x是否與pattern相匹配，該函數還可以提供一個可選的參數match_option字元串說明默認的匹配選項。match_option的取值如下：

'c' 說明在進行匹配時區分大小寫（預設值）；

'i' 說明在進行匹配時不區分大小寫；

'n' (.)點號能表示所有單個字元,包括換行(俺還不知道什麼地方有用到換行.只知道sql裡面可以用chr(10)表示換行、

'm' 字元串存在換行的時候當作多行處理.這樣$就可匹配每行的結尾.不然的話$只匹配字元串最後的位置、

示例：

select * from emp where regexp_like(ename,'^a[a-z]*n$');

可以查找ename中以a開頭以n結尾的行.例如ename為arwen或arwin或anden.但Arwen不能被匹配.因為默認是區分大小寫.如果是

select * from emp where regexp_like(ename,'^a[a-z]*n$','i')

則可以查找ename為Arwen的行記錄。

2、regexp_instr:

REGEXP_INSTR(x,pattern[,start[,occurrence[,return_option[, match_option]]]])用於在x中查找pattern。返回pattern在x中出現的位置。匹配位置從1開始。可以參考字元串函數 INSTR()，參數相關：

'start' 開始查找的位置；

'occurrence' 說明應該返回第幾次出現pattern的位置；

'return_option' 說明應該返回什麼整數。若該參數為0，則說明要返回的整數是x中的一個字元的位置；若該參數為非0的整數，則說明要返回的整數為x中出現在pattern之後 的字元的位置；

'match_option' 修改默認的匹配設置.與regexp_like裡面的相同.

示例：

DECLARE

V_RESULT INTEGER ;

BEGIN

SELECT REGEXP_INSTR('hello world','o',1,1,0) INTO V_RESULT

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(V_RESULT);

END;

結果為5，即字母o第一個次出現的位置。

如果regexp_instr('hello world','o',1,1,n)其中n為除0之外的整數。比如1,3。則結果為6.表示第一次出現字母o的後面一個字元的位置。

如果regexp_instr('hello world','o',1,2,0)則結果為9.表示第二次出現字母o的位置.

3、regexp_replace:

REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])用於在x中查找pattern，並將其替換為replae_string。可以參考字元串函數 REPLACE()，參數同REGEXP_INSTR函數

示例：

DECLARE

V_RESULT varchar2(90);

BEGIN

SELECT REGEXP_REPLACE('hello world','o','x',1,1) INTO V_RESULT

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(V_RESULT);

END;

結果為hellx world.

如果REGEXP_REPLACE('hello world','o','x'),則結果為hellx wxrld.

如果 REGEXP_REPLACE('hello world','o','x',1,2)則結果為hello wxrld.

4、regexp_substr:

REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])用於在x中查找pattern並返回。可以參考字元串函數 SUBSTR()，參數同REGEXP_INSTR函數.

例如：

DECLARE

V_RESULT VARCHAR2(255);

BEGIN

SELECT REGEXP_SUBSTR('hello world','l{2}') INTO V_RESULT

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(V_RESULT);

END ;

結果為ll

查詢到匹配的字元串才返回匹配的字元.沒查到就返回空。

B. 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 的早期版本兼容

二 使用 % 通配符

例如，此查詢將顯示資料庫中所有的系統表，因為它們都以字母 sys 開始：
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'sys% '
若要查閱非系統表的所有對象，請使用 NOT LIKE 'sys% '。如果共有 32 個對象且 LIKE 找到 13 個與模式匹配的名稱，則 NOT LIKE 將找到 19 個與 LIKE 模式不匹配的對象

三 使用escape字句的模式匹配

可搜索包含一個或多個特殊通配符的字元串。例如，customers 資料庫中的 discounts 表可能存儲含百分號 (%) 的折扣值。若要搜索作為字元而不是通配符的百分號，必須提供 ESCAPE 關鍵字和轉義符。例如，一個樣本資料庫包含名為 comment 的列，該列含文本 30%。若要搜索在 comment 列中的任何位置包含字元串 30% 的任何行，請指定由 WHERE comment LIKE '%30!%% ' ESCAPE '! ' 組成的 WHERE 子句。如果不指定 ESCAPE 和轉義符，SQL Server 將返回所有含字元串 30 的行。

下例使用 ESCAPE 子句和轉義符查找 mytbl2 表的 c1 列中的精確字元串 10-15%

USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2 ')
DROP TABLE mytbl2
GO
USE pubs
GO
CREATE TABLE mytbl2
(
c1 sysname
)
GO
INSERT mytbl2 VALUES ( 'Discount is 10-15% off ')
INSERT mytbl2 VALUES ( 'Discount is .10-.15 off ')
GO
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15!% off% ' ESCAPE '! '
GO

四 使用 [] 通配符

下例查找姓為 Carson、Carsen、Karson 或 Karsen 的作者所在的行。
USE pubs
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE '[CK]ars[eo]n '
ORDER BY au_lname ASC, au_fname ASC