sql使用正則表達式

1. sql 正則表達式匹配

SELECT
REPLACE(
REPLACE(
SUBSTRING_INDEX(B,
'.',
3),
SUBSTRING_INDEX(B,
'.',
1),
''),'.','')
as
你的值
from
TableA;
-----------------------------------------------------------------------------
SELECT
REPLACE(
REPLACE(
SUBSTRING_INDEX('123.456.789.0123',
'.',
3),SUBSTRING_INDEX('123.456.789.0123',
'.',
1),''
),
'.','')
其中
SUBSTRING_INDEX('123.456.789.0123',
'.',
3)
的值為
123.456.789
其中
SUBSTRING_INDEX('123.456.789.0123',
'.',
1)
的值為
123
然後進行替換，再替換掉點符號，就可得到，這樣比正則效率高。

2. SQL的正則表達式

注意：正則表達式後面需用''括起來，因為正則表達式是針對「文本」的匹配。

正則表達式REGEXP可以理解為可支持更多規則/通配符的LIKE，可以對檢索內容進行更強的控制。LIKE本身只能和%及_這兩種通配符連接進行粗略的搜索，而REGEXP可以支持更多規則，比如.是和%一樣的可匹配任意一個字元的正則模式，[]可匹配一個范圍，如REGEXP '[1-5] ton'可以搜索出來1 ton、2 ton、3 ton、4 ton、5 ton。

一些正則模式：

.  ：匹配任意一個字元，類似於%

| ：類似於OR

[] ：字元集合，可用|隔開表or選項，也可用-定義范圍

[^] ：類似於NOT，匹配非[]框內的

定位元字元 ：

^ ：匹配輸入字元串的開始位置【^有兩種用法，一種是開始位置，一種和[]連在一起表示NOT】

$ ：匹配輸入字元串的結束位置

[[:<:]] ：詞的開始

[[:<:]] ：詞的結尾

字元類 （預定義的字元集，類似「快捷鍵」）：

重復元字元 ：

* ：0次或多次匹配

+ ：1次或多次匹配（等於{1,}）

? ：匹配它前面的任何字元0次或1次（等於{0,1}）

{n} ：指定數目的匹配

{n,} ：不少於指定數目的匹配

{n,m} ：匹配數目的范圍（m不超過255）

正則表達式略復雜，但用好了會非常方便，所以務必把各種正則模式記熟。

關於特殊字元所在位置會造成不同影響，有個小例子：

^[0-9\\.]表示查找以0-9里任意一個數字和「.」開頭的字元串

[^0-9\\.]表示除了「0-9.」這個單詞外的任意字元串

轉義

如果想搜出代表正則模式字元本身，比如想查找「.」，如果直接輸REGEXP '.'，那麼查找出來的將是全部行，因為「.」表示任意字元；又比如想查找「|」，直接輸REGEXP '|'，那麼查找不出結果，因為正則表達式認為這是or的含義，此時需要用到「轉義」的功能，即在想查找的特殊字元前輸入兩條斜杠——「//」，告訴正則表達式現在要查找的是特殊字元本身，而不是它所代表的含義。

另外還有一些在SQL練習中碰到的其他注意事項：

NOT只對單個欄位有效，如果想多重否定，需要在每個欄位前都加上NOT，比如 WHERE vend_id NOT IN(1002,1003)  AND prod_price NOT IN (5.99);

通配符%可以任意長度的字元，包括0字元，但不能匹配出NULL；_只能匹配單個字元。注意盡量少使用通配符，並且盡量不要在開頭使用，避免搜索時間過長。

3. SQL Server 2008 R2如何使用正則表達式搜索

使用正則表達式進行查找若要在「快速查找」、「在文件中查找」、「快速替換」或「在文件中替換」 操作過程中，在「查找內容」欄位啟用正則表達式，請在「查找選項」下選中「使用」，再選擇「正則表達式」。「查找內容」欄位旁邊的「引用列表」三角形按鈕將變為可用狀態。單擊此按鈕可顯示一組最常用的正則表達式。如果選擇了表達式生成器中的某個項，則可將該項插入「查找內容」字元串。注意「查找內容」字元串中使用的正則表達式與 Microsoft .NET Framework 編程中的有效正則表達式相比，語法上存在差異。例如，在「查找和替換」對話框中，大括弧 {} 用於表示帶標記的表達式。所以，表達式「zo{1}」將匹配所有「zo」後帶標記 1 的匹配項，如「Alonzo1」和「Gonzo1」。但在 .NET Framework 中,{} 符號用於表示量詞。因此，表達式「zo{1}」將匹配所有「z」後跟一個「o」的匹配項，如匹配「zone」，但不匹配「zoo」。下表對「引用列表」中提供的正則表達式進行了說明。 表達式語法說明任何單個字元.匹配除換行符外的所有單一字元。零個或更多*匹配零或更多前導表達式的匹配項，執行所有可能的匹配。一個或更多+匹配至少一個前導表達式的匹配項。行首^僅匹配行首位置的字元串。行尾$僅匹配行尾位置的字元串。字首<僅匹配文本中以此開頭的字。字尾>僅匹配文本中以此結尾的字。分行符
匹配與平台無關的換行符。在替換表達式中，插入一個換行符。在集合中的任何一個字元[]匹配[] 中的任何一個字元。若要指定字元范圍，請列出起始字元和結束字元，並用短劃線 (-) 分隔，如 [a-z]。不在集合中的任何一個字元[^...]匹配不在 ^ 之後的字元集中的任何一個字元。或|匹配「或」符號 (|) 兩旁的任一表達式。常用於組中。例如，(sponge|mud) bath 可以匹配「sponge bath」和「mud bath」。Escape\將反斜杠 (\) 後的字元作為文字匹配。此表達式允許您搜索正則表達式標記中使用的符號，如 { 和 ^。例如， \^ 可用於查找字元 ^。標記表達式{}匹配用括起來的表達式內的文本。C/C++ 標識符:i匹配表達式 ([a-zA-Z_$][a-zA-Z0-9_$]*)。用引號括起來的字元串:q匹配表達式 (("[^"]*")|('[^']*'))。空格或製表符:b匹配空格或製表符。整數:z匹配表達式 ([0-9]+)。「引用列表」中只列出了部分可在「查找和替換」操作中使用的正則表達式。還可以將下列任一正則表達式插入「查找內容」字元串： 表達式語法說明最少- 零個或更多@匹配零個或更多前導表達式的匹配項，匹配盡可能少的字元。最少- 一個或更多#匹配一個或更多前導表達式的匹配項，匹配盡可能少的字元。重復n 次^n匹配前導表達式的 n 個匹配項。例如，[0-9]^4 匹配所有四位數序列。分組()將子表達式分組第n 個標記文本
在「查找或替換」表達式中，指示匹配第 n 個標記表達式的文本，其中 n 為 1 到 9 之間的數字。在「替換」表達式中，\0 將插入完整的匹配文本。右對齊欄位\(w,n)在「替換」表達式中，在寬度至少為 w 個字元的欄位中，右對齊第 n 個標記表達式。左對齊欄位\(-w,n)在「替換」表達式中，在寬度至少為 w 個字元的欄位中，左對齊第 n 個標記表達式。阻止匹配~(X)如果在表達式的此處出現 X，則阻止匹配。例如，real~(ity) 匹配「realty」和「really」中的「real」，但不匹配「reality」中的「real」。字母數字字元:a匹配表達式 ([a-zA-Z0-9])。字母字元:c匹配表達式 ([a-zA-Z])。十進制數字:d匹配表達式 ([0-9])。十六進制數:h匹配表達式 ([0-9a-fA-F]+)。有理數:n匹配表達式 (([0-9]+.[0-9]*)|([0-9]*.[0-9]+)|([0-9]+))。字母字元串:w匹配表達式 ([a-zA-Z]+)。Escape\eUnicode U+001B。響鈴\gUnicode U+0007。Backspace\hUnicode U+0008。製表符\t匹配製表符，Unicode U+0009。Unicode 字元\x#### 或 \u####匹配由 Unicode 值確定的字元，其中 #### 為十六進制數字。可以使用 ISO 10646 碼位或給出代理項對值的兩個 Unicode 碼位，指定基本多語言面（即代理項）以外的字元。下表列出了按標准 Unicode 字元屬性匹配時的語法。雙字母縮寫和 Unicode 字元屬性資料庫中列出的縮寫相同。可以將這些縮寫指定為字元集的一部分。例如，表達式 [:Nd:Nl:No] 匹配任何數字。 表達式語法說明大寫字母:Lu匹配任何單個大寫字母。例如，:Luhe 匹配「The」，但不匹配「the」。小寫字母:Ll匹配任何單個小寫字母。例如，:Llhe 匹配「the」，但不匹配「The」。標題大寫字母:Lt匹配一個大寫字母後跟一個小寫字母的字元，例如 Nj 和 Dz。修飾字母:Lm匹配字母或標點符號（如逗號、交叉重音符和秒號），這些字母或標點符號是用於修飾前導字母的。其他字母:Lo匹配其他字母，如歌特字母 ahsa。十進制數字:Nd匹配十進制數字（如 0-9）及其全形對等數字。字母數字:Nl匹配字母數字，如羅馬數字和零的表意數字。其他數字:No匹配其他數字，如舊斜體數字一。左標點:Ps匹配左標點，如左小括弧和左大括弧。右標點:Pe匹配右標點，如右小括弧和右大括弧。左引號:Pi匹配左雙引號。右引號:Pf匹配單引號和右雙引號。劃線號:Pd匹配劃線號。連接號:Pc匹配下劃線符號。其他標點:Po匹配(,)、?、"、!、@、#、%、&、*、\、(:)、(;)、' 和 /。空格分隔符:Zs匹配空格。行分隔符:Zl匹配Unicode 字元 U+2028。段落分隔符:Zp匹配Unicode 字元 U+2029。不佔位標記:Mn匹配不佔位標記。合並標記:Mc匹配合並標記。封閉標記:Me匹配封閉標記。數學符號:Sm匹配+、=、~、|、< 和 >。貨幣符號:Sc匹配$ 和其他貨幣符號。修飾符號:Sk匹配修飾符號，如抑揚符、重音符和長音符。其他符號:So匹配其他符號，如版權符號、段落標記和度數符號。其他控制:Cc匹配行尾。其他格式:Cf格式化控制字元，如雙向控制字元。代理項:Cs匹配代理項對的一半。其他專門用途:Co匹配任何專用區域的字元。其他未分配字元:Cn匹配未映射到 Unicode 字元的字元。除了標準的 Unicode 字元屬性以外，還可以將下列附加屬性指定為字元集的一部分。 表達式語法說明字母:Al匹配任何單字元。例如，:Alhe 匹配「The」、「then」 和「reached」之類的字。Numeric:Nu匹配任何數字。標點:Pu匹配任意一個標點符號，如 ?、@、' 等等。空格:Wh匹配任何類型的空格，包括印刷空格和表意空格。雙向:Bi匹配自右至左書寫的字元，如阿拉伯語和希伯萊語字元。朝鮮文字:Ha匹配朝鮮文字字母和組合字母。平假名:Hi匹配平假名字元。片假名:Ka匹配片假名字元。象形/漢字/日文漢字:Id匹配象形字元，如漢字和日文漢字。用於查找文本的正則表達式Expression Studio 4.0 其他版本 此主題尚未評級 - 評價此主題 通過使用正則表達式，可以在 Microsoft Expression Web 中執行繁復的查找和替換操作。如果您不知道所要查找的確切文本或代碼，或者要查找文本或代碼字元串的多個匹配項，則可以使用正表達式。正則表達式是一種文本模式，它可以描述所要查找的文本的一種或多種變體。正則表達式由特定字元（如字母「a」到「z」）和描述文本模式的特殊字元（如星號「*」）組成。例如，要在網站中查找「page」的所有變體，可以搜索「page*」。這樣，Expression Web 將在網站中查找「page」、「pages」、「pager」以及以「page」開頭的任何其他單詞的所有實例。在搜索中使用正則表達式時，特定的規則將控制哪些字元組合將執行特定的匹配操作。每種正則表達式（或正則表達式的組合）都稱為「語法」。可以在一個語法中使用多個正則表達式以精確匹配要搜索的目標。若要使用正則表達式，請參閱使用查找和替換編輯多個網頁。正則表達式語法 語法表達式說明.任意字元 用作換行符 (
) 以外的任何單個列印或非列印字元的通配符。例如，正則表達式 c.t 可匹配 cat、c t、cot 等字元串，但不能匹配 cost。在本例中，句點 (.) 是一個代表單個字元的通配符。它出現在字母「c」和「t」之間，因此字元「c」和「t」之間的任何單個字元都與表達式匹配 - 即使是空格也一樣。*最大為零或更多 用於匹配在此表達式之前出現零次或更多次的某個字元，可與盡可能多的字元相匹配。正則表達式 .* 用於匹配某個出現零次或更多次的字元。例如，正則表達式 b.*k 可匹配 book、back、black、blank 和 buck。在本例中，將句點 (.) 和星號 (*) 組合成一個語法。句號 (.) 緊挨在星號 (*) 表達式之前。星號 (*) 可匹配「b」和「k」之間任意出現零次或更多次的字元。句點 (.) 用作「b」和「k」之間字元的通配符。在本例中，它表示「b」和「k」之間的任意字元都可以反復出現。+最大為一或更多 用於匹配在此表達式之前出現一次或更多次的某個字元，可與盡可能多的字元相匹配。正則表達式 .+ 用於匹配某個出現一次或更多次的字元。例如，正則表達式 bo+. 可匹配 bob、book 和 boot。在本例中，將句點 (.) 和加號 (+) 組合成一個語法。句號 (.) 緊挨在加號 (+) 表達式之後。加號 (+) 可與出現一次或更多次的字母「o」相匹配。句點 (.) 用作每個單詞的最後一個字元的通配符。在本例中，該字元為「b」、「k」和「t」。@最小為零或更多 用於匹配在此表達式之前出現零次或更多次的某個字元，可與盡可能少的字元相匹配。正則表達式 .@ 用於匹配某個出現零次或更多次的字元。例如，正則表達式 a.@x 可匹配「abxbxb」中的「abx」和「acxcxc」中的「acx」。在本例中，將句號 (.) 和 @ 符號組合成一個語法。句點 (.) 緊挨在 @ 符號表達式之前。@ 符號可與「a」和「x」之間出現零次或更多次的任意字元相匹配。在本例中，句點 (.) 用作字元「a」和「x」之間的字元「b」和「c」的通配符。#最小為一或更多 用於匹配在此表達式之前出現一次或更多次的某個字元，可與盡可能少的字元相匹配。例如，正則表達式 si.#er 可匹配「sicker」或「silkier」。在本例中，將句號 (.) 和井號 (#) 組合成一個語法。句點 (.) 緊挨在井號 (#) 表達式之前。井號 (#) 可與「si」和「er」之間出現一次或更多次的任意字元相匹配。句點 (.) 用作單詞 sicker 中字元「c」和「k」的通配符，以及單詞 silkier 中「l」、「k」和「i」的通配符。[ ]字元集 用於匹配括弧 ([ ]) 內的任意一個字元。可以使用連字元 (-) 指定字元的范圍，例如 [a-z]。例如：正則表達式 c[aou]t 可以匹配 cat、cot 和 cut，但不能匹配 cet 或 cit。正則表達式 [0-9] 表示匹配任意一個數字。也可以指定多個字母範圍。正則表達式 [A-Za-z] 表示匹配所有大寫和小寫字母。^行首 定位於行首匹配。例如，正則表達式 ^When in 匹配以「When in」開頭且出現於行首的任意字元串，例如「When in the course of human events」或「When in town, call me」。然而，此正則表達式不匹配出現於行首的「What and when in the course of human events」。$行尾 定位於行尾匹配。例如，正則表達式 professional$ 可匹配字元串「He is a professional」，但不能匹配字元串「They are a group of professionals」。^^文件開頭 定位於文件開頭匹配。僅適用於在源代碼或文本文件中搜索文本。例如，若要匹配文件開頭的第一個 HTML 標記，請使用此正則表達式：^^$$文件末尾 定位於文件末尾匹配。僅適用於在源代碼或文本文件中搜索文本。例如，若要匹配文件末尾的最後一個 HTML 標記（標記後沒有空格），可使用此正則表達式：$$|或 表示在兩項之間進行選擇，即匹配「或」符號 (|) 前面或後面的表達式。例如，正則表達式 (him|her) 匹配下列各項：「it belongs to him」或者「it belongs to her」但不匹配行「it belongs to them」。\轉義特殊字元 用於匹配反斜線 (\) 後面的字元。這樣，就可以查找正則表達式語法中使用的字元（如左大括弧 ({) 或脫字型大小 (^)）或其他一些特殊字元。例如，使用 \$ 可匹配美元符號 ($) 字元，而不是執行用於「定位於行尾」的正則表達式。同樣，使用 \. 可匹配句號 (.) 字元，而不是像句號 (.) 正則表達式一樣匹配任意一個字元。{}標記表達式 對與括弧內表達式匹配的文本加標記。可以使用 \N 在「查找」表達式中匹配出現在另一處的標記文本或者將標記文本插入「替換」表達式。例如，假設要查找連續出現兩次的單詞。若要搜索，請使用此表達式：{.#} \1假設連續出現的單詞由一個空格隔開，則需要在右大括弧 (}) 與反斜線 (\) 之間添加一個空格。在本例中，將井號 (#)、句號 (.) 和大括弧 ({}) 組合成一個語法。在此表達式中，.# 表示任何連續的字元。因為這部分表達式位於大括弧 ({}) 內，所以連續的字元將被加上標記，並被引用為 \1。此表達式將查找後跟空格以及字元與其完全相同的任意連續字元。\N第N 個標記表達式 在「查找」表達式中，\N 匹配由第 N 個標記表達式指定的文本，其中 N 是 1 到 9 的數字。在「替換」表達式中，\N 插入由第 N 個標記表達式匹配的文本，其中 N 是 1 到 9 之間的數字。\0 插入整個「查找」表達式指定的文本。例如，假設要查找連續出現兩次的單詞，並使用一個單詞替換它們。若要搜索，請使用此表達式：.#} \l假設連續出現的單詞由一個空格隔開，則需要在右大括弧 (}) 與反斜線 (\) 之間添加一個空格。在本例中，將井號 (#)、句號 (.) 和大括弧 ({}) 組合成一個語法。若要替換，請使用下列表達式：\l\1 表示在查找字元串中的第一對大括弧內找到的內容。通過在替換操作中使用 \1，實際上是將連續出現兩次的單詞替換為一個單詞。( )組表達式 標記子表達式的開始和結束。子表達式指括在 ( ) 中的正則表達式，例如表達式：(ha)+。在本例中，將加號 (+) 與括弧 ( ) 組表達式組合成一個語法。子表達式是 (ha)，因為它括在括弧 ( ) 內。添加加號 (+) 後，該表達式可查找重復的字母對。加號 (+) 表示「ha」出現一次或更多次。此表達式可匹配下列各項：「haha」和「hahaha」。~x防止匹配 當x 出現在表達式中的此位置時會防止匹配。例如，正則表達式 real~(ity) 可匹配「realty」和「really」中的「real」，但不能匹配「reality」中的「real」。
換行符 用於匹配「代碼」視圖中的新行，或「設計」視圖中的 <br>。語法(
) 是匹配所有換行符的簡便方法。\t製表符 用於匹配單個製表符。例如，如果要查找所有位於行首的製表符，可使用此正則表達式：^\t+在本例中，將脫字型大小 (^)、加號 (+) 和製表符 (\t) 組合成一個語法。脫字型大小 (^) 位於單個製表符表達式之前，用於匹配位於行首的所有製表符。加號 (+) 表示匹配一個或多個製表符。[^]任何一個不屬於該集合的字元 用於匹配不包含在脫字型大小 (^) 之後的字元集內的任何字元。例如，若要匹配范圍之外的任何字元，可使用脫字型大小 (^) 作為左括弧後面的第一個字元。表達式 [^269A-Z] 匹配除 2、6、9 以及任何大寫字母之外的所有字元。n重復表達式 用於匹配脫字型大小 (^) 之前出現 n 次的表達式。例如，若 n 為 4，表達式 [0-9]^4 匹配任何 4 位數序列。在本例中，將字元集 ([ ]) 語法與重復 (^n) 語法組合，以展示正則表達式的更實用的方法。:a字母數字字元 用於匹配表達式 [a-zA-Z0-9]。可以使用表達式 [a-zA-Z0-9] 匹配出現一次的字母（大寫或小寫）或數字。也稱為「字母數字匹配項」。可使用簡寫表達式 :a 來代替 [a-zA-Z0-9]。:b空格 用於匹配代碼或文本中的任何空格。例如，若要匹配行首的單個空格字元，請使用此正則表達式：^:b:c字母字元 用於匹配表達式 [a-zA-Z]。使用此表達式時，可以匹配所有大寫或小寫字母。可以使用簡寫表達式 :c 來代替所有 [a-zA-Z]。:d十進制數 用於匹配表達式 [0-9]。利用此表達式可以匹配任何數字。例如，假設要在文本文件中查找美國的社會福利號。美國的社會福利號的格式為 999-99-9999。:d^3-:d^2-:d^4 或 [0-9]^3-[0-9]^2-[0-9]^4（使用 [0-9] 表示）可返回相同的結果。可使用簡寫表達式 :d 來代替所有 [0-9]。:h十六進制數 用於匹配表達式 [0-9a-fA-F]+使用此表達式可匹配由「A」和「F」之間的大寫或小寫字母和任意數字組成的十六進制數。例如，假設網站中的網頁使用了多種不同的背景色，而您要將這些網頁的顏色改為黑色（即 000000）。但是，您不知道現有顏色的十六進制值。使用此正則表達式可查找所有的現有十六進制值：\#:h可以使用 [0-9a-fA-F] 進行搜索，但在本例中，將反斜線 (\)、井號 (#) 和十六進制數字 (:h) 語法組合在一起。\# 匹配非表達式的井號 (#)，而 :h 匹配任何十六進制字元序列。若要替換現有十六進制數值，可鍵入所需背景顏色的十六進制數值： 000000:i標識符 用於匹配表達式 [a-zA-Z_$][a-zA-Z0-9_$]*處理代碼時，如果需要匹配所有程序標識符，可使用簡寫表達式 :i，而無需鍵入上述冗長的表達式。:n有理數 用於匹配表達式 ([0-9]+\.[0-9]*)|([0-9]*\.[0-9]+)|([0-9]+)如果需要匹配帶小數點的整個數值，可使用簡寫表達式 :n，而無需鍵入上述冗長的表達式。:q帶引號的字元串 用於匹配表達式 ("[~"]*")|('[~']*')如果需要匹配加引號的所有引號，可使用簡寫表達式 :q，而無需鍵入上述冗長的表達式。:w字母字元串 用於匹配表達式 [a-zA-Z]+此語法是一種可匹配一個或多個大寫或小寫字母字元的簡便方法。:z整數 用於匹配表達式 [0-9]+此語法是一種可匹配任何整數的簡便方法。

4. 如何在sql server中使用正則表達式

大致步驟是：
1.下載他提供的那個壓縮包，裡面有源代碼和安裝腳本
2.將DLL復制到SQL
Server規定的目錄
3.運行INSTALL.sql這個腳本
大致使用的效果如下
SELECT
master.dbo.fn_pcre_match('[email protected]','^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$')
這句腳本的意思是，根據後面的正則表達式（一個email的規則）匹配前面的字元串.
如果返回1的話，表示匹配到了，否則返回0.

5. 如何在sql server中使用正則表達式

大致步驟是：
1.下載他提供的那個壓縮包，裡面有源代碼和安裝腳本
2.將DLL復制到SQL Server規定的目錄
3.運行INSTALL.sql這個腳本
大致使用的效果如下
SELECT master.dbo.fn_pcre_match('[email protected]','^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$')
這句腳本的意思是，根據後面的正則表達式（一個email的規則）匹配前面的字元串.
如果返回1的話，表示匹配到了，否則返回0.