熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

在Oracle使用正規表達式編寫更好的SQL語句

2013-11-13 16:22:32  來源: Oracle 

  Oracle Database g 的一個新特性大大提高了您搜索和處理字符數據的能力這個特性就是正規表達式是一種用來描述文本模式的表示方法很久以來它已在許多編程語言和大量 UNIX 實用工具中出現過了
  
  Oracle 的正規表達式的實施是以各種 SQL 函數和一個 WHERE 子句操作符的形式出現的如果您不熟悉正規表達式那麼這篇文章可以讓您了解一下這種新的極其強大然而表面上有點神秘的功能已經對正規表達式很熟悉的讀者可以了解如何在 Oracle SQL 語言的環境中應用這種功能
  
  什麼是正規表達式?
  
  正規表達式由一個或多個字符型文字和/或元字符組成在最簡單的格式下正規表達式僅由字符文字組成如正規表達式 cat它被讀作字母 c接著是字母 a 和 t這種模式匹配 catlocation 和 catalog 之類的字符串元字符提供算法來確定 Oracle 如何處理組成一個正規表達式的字符當您了解了各種元字符的含義時您將體會到正規表達式用於查找和替換特定的文本數據是非常強大的
  
  驗證數據識別重復關鍵字的出現檢測不必要的空格或分析字符串只是正規表達式的許多應用中的一部分您可以用它們來驗證電話號碼郵政編碼電子郵件地址社會安全號碼IP 地址文件名和路徑名等的格式此外您可以查找如 HTML 標記數字日期之類的模式或任意文本數據中符合任意模式的任何事物並用其它的模式來替換它們
  
  用 Oracle Database g 使用正規表達式
  
  您可以使用最新引進的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTRREGEXP_SUBSTR 以及 REGEXP_REPLACE 函數來發揮正規表達式的作用您將體會到這個新的功能如何對 LIKE 操作符和 INSTRSUBSTR 和 REPLACE 函數進行了補充實際上它們類似於已有的操作符但現在增加了強大的模式匹配功能被搜索的數據可以是簡單的字符串或是存儲在數據庫字符列中的大量文本正規表達式讓您能夠以一種您以前從未想過的方式來搜索替換和驗證數據並提供高度的靈活性
  
  正規表達式的基本例子
  
  在使用這個新功能之前您需要了解一些元字符的含義句號 () 匹配一個正規表達式中的任意字符(除了換行符)例如正規表達式 ab 匹配的字符串中首先包含字母 a接著是其它任意單個字符(除了換行符)再接著是字母 b字符串 axbxaybx 和 abba 都與之匹配因為在字符串中隱藏了這種模式如果您想要精確地匹配以 a 開頭和以 b 結尾的一條三個字母的字符串則您必須對正規表達式進行定位脫字符號 (^) 元字符指示一行的開始而美元符號 ($) 指示一行的結尾(參見表 因此 正規表達式 ^ab$ 匹配字符串 aababb 或 axb將這種方式與 LIKE ⊃Ù×÷·û提供的類似的模式匹配 a_b 相比較其中 (_) 是單字符通配符
  
  默認情況下一個正規表達式中的一個單獨的字符或字符列表只匹配一次為了指示在一個正規表達式中多次出現的一個字符您可以使用一個量詞它也被稱為重復操作符如果您想要得到從字母 a 開始並以字母 b 結束的匹配模式則您的正規表達式看起來像這樣^a*b$* 元字符重復前面的元字符 () 指示的匹配零次一次或更多次LIKE 操作符的等價的模式是 a%b其中用百分號 (%) 來指示任意字符出現零次一次或多次
  
  表 給出了重復操作符的完整列表注意它包含了特殊的重復選項它們實現了比現有的 LIKE 通配符更大的靈活性如果您用圓括號括住一個表達式這將有效地創建一個可以重復一定次數的子表達式例如正規表達式 b(an)*a 匹配 babanabananayourbananasplit 等
  
  Oracle 的正規表達式實施支持 POSIX (可移植操作系統接口)字符類參見表 中列出的內容這意味著您要查找的字符類型可以非常特別假設您要編寫一條僅查找非字母字符的 LIKE 條件 — 作為結果的 WHERE 子句可能不經意就會變得非常復雜
  
  POSIX 字符類必須包含在一個由方括號 ([]) 指示的字符列表中例如正規表達式 [[:lower:]] 匹配一個小寫字母字符而 [[:lower:]]{} 匹配五個連續的小寫字母字符
  
  除 POSIX 字符類之外您可以將單獨的字符放在一個字符列表中例如正規表達式 ^ab[cd]ef$ 匹配字符串 abcef 和 abdef必須選擇 c 或 d
  
  除脫字符 (^) 和連字符 () 之外字符列表中的大多數元字符被認為是文字正規表達式看起來很復雜這是因為一些元字符具有隨上下文環境而定的多重含義^ 就是這樣一種元字符如果您用它作為一個字符列表的第一個字符它代表一個字符列表的非因此[^[:digit:]] 查找包含了任意非數字字符的模式而 ^[[:digit:]] 查找以數字開始的匹配模式連字符 () 指示一個范圍正規表達式 [am] 匹配字母 a 到字母 m 之間的任意字母但如果它是一個字符行中的第一個字符(如在 [afg] 中)則它就代表連字符
  
  之前的一個例子介紹了使用圓括號來創建一個子表達式它們允許您通過輸入更替元字符來輸入可更替的選項這些元字符由豎線 (|) 分開
  
  例如正規表達式 t(a|e|i)n 允許字母 t 和 n 之間的三種可能的字符更替匹配模式包括如 tantentin 和 Pakistan 之類的字但不包括 teenmountain 或 tune作為另一種選擇正規表達式 t(a|e|i)n 也可以表示為一個字符列表 t[aei]n 匯總了這些元字符雖然存在更多的元字符但這個簡明的概述足夠用來理解這篇文章使用的正規表達式
  
  REGEXP_LIKE 操作符
  
  REGEXP_LIKE 操作符向您介紹在 Oracle 數據庫中使用時的正規表達式功能 列出了 REGEXP_LIKE 的語法
  
  下面的 SQL 查詢的 WHERE 子句顯示了 REGEXP_LIKE 操作符它在 ZIP 列中搜索滿足正規表達式 [^[:digit:]] 的模式它將檢索 ZIPCODE 表中的那些 ZIP 列值包含了任意非數字字符的行
  
  SELECT zip
  FROM zipcode
  WHERE REGEXP_LIKE(zip [^[:digit:]])
  ZIP
  
  ab
  xy
  ab
  abcxy
  
  這個正規表達式的例子僅由元字符組成更具體來講是被冒號和方括號分隔的 POSIX 字符類 digit第二組方括號(如 [^[:digit:]] 中所示)包括了一個字符類列表如前文所述需要這樣做是因為您只可以將 POSIX 字符類用於構建一個字符列表
  
  REGEXP_INSTR 函數
  
  這個函數返回一個模式的起始位置因此它的功能非常類似於 INSTR 函數新的 REGEXP_INSTR 函數的語法在表 中給出這兩個函數之間的主要區別是REGEXP_INSTR 讓您指定一種模式而不是一個特定的搜索字符串因而它提供了更多的功能接下來的示例使用 REGEXP_INSTR 來返回字符串 Joe Smith Berry Lane San Joseph CA 中的五位郵政編碼模式的起始位置如果正規表達式被寫為 [[:digit:]]{}則您將得到門牌號的起始位置而不是郵政編碼的因為 是第一次出現五個連續數字因此您必須將表達式定位到該行的末尾正如 $ 元字符所示該函數將顯示郵政編碼的起始位置而不管門牌號的數字個數
  
  SELECT REGEXP_INSTR(Joe Smith Berry Lane San Joseph CA
  [[:digit:]]{}$)
  AS rx_instr
  FROM dual
  RX_INSTR
  
  
  
  編寫更復雜的模式
  
  讓我們在前一個例子的郵政編碼模式上展開以便包含一個可選的四位數字模式您的模式現在可能看起來像這樣[[:digit:]]{}([[:digit:]]{})?$如果您的源字符串以 位郵政編碼或 位 + 位郵政編碼的格式結束則您將能夠顯示該模式的起始位置
  
  SELECT REGEXP_INSTR(Joe Smith Berry Lane San Joseph CA
   [[:digit:]]{}([[:digit:]]{})?$)
  AS starts_at
  FROM dual
  STARTS_AT
  
  
  
  在這個示例中括弧裡的子表達式 ([[:digit:]]{}) 將按 ? 重復操作符的指示重復零次或一次此外企圖用傳統的 SQL 函數來實現相同的結果甚至對 SQL 專家也是一個挑戰為了更好地說明這個正規表達式示例的不同組成部分 包含了一個對單個文字和元字符的描述
  
  REGEXP_SUBSTR 函數
  
  ·Ç⊃£ÀàËÆÓÚ SUBSTR 函數的 REGEXP_SUBSTR 函數用來提取一個字符串的一部分 顯示了這個新函數的語法在下面的示例中匹配模式 [^]* 的字符串將被返回該正規表達式搜索其後緊跟著空格的一個逗號然後按 [^]* 的指示搜索零個或更多個不是逗號的字符最後查找另一個逗號這種模式看起來有點像一個用逗號分隔的值字符串
  
  SELECT REGEXP_SUBSTR(first field second field third field
   [^]*)
  FROM dual
  REGEXP_SUBSTR(FIR
  
   second field
  
  REGEXP_REPLACE 函數
  
  讓我們首先看一下傳統的 REPLACE SQL 函數它把一個字符串用另一個字符串來替換假設您的數據在正文中有不必要的空格您希望用單個空格來替換它們利用 REPLACE 函數您需要准確地列出您要替換多少個空格然而多余空格的數目在正文的各處可能不是相同的下面的示例在 Joe 和 Smith 之間有三個空格REPLACE 函數的參數指定要用一個空格來替換兩個空格在這種情況下結果在原來的字符串的 Joe 和 Smith 之間留下了一個額外的空格
  
  SELEC
From:http://tw.wingwit.com/Article/program/Oracle/201311/18225.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.