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

Oracle10gR2中用正則表達式搜索文本

2013-11-13 15:59:56  來源: Oracle 
Oracle g Release 為了與文本字符串中的模式相匹配添加了對POSIX正則表達式的支持Release 用一些附加的可以在Perl找到的元字符擴充了最初的模式元字符目錄如果你正在用Oracle gR開發腳本那麼就看看本文裡這些搜索文本的有效捷徑

  首先簡短概述一下:函數REGEXP_LIKE針對一種模式搜索一個字符列返回一個TRUE或FALSE的邏輯值你可以在SQL SELECT語句的WHERE子句中用它來限定行數或者在PL/SQL塊中檢查數據的有效性相似的函數——REGEXP_REPLACEREGEXP_SUBSTR和REGEXP_INSTR——都能夠在執行其他常見的字符串操作時接受POSIX正則表達式

  Perl為POSIX表達式添加了一些簡短的模式例如在標准的POSIX正則表達式中你用符號[[:digit:]]來表示一個從的阿拉伯數字在Perl中它被簡化成\d大括號表示一次重復計數因此\d{}規定了三個阿拉伯數字要表示你想要一個除阿拉伯數字之外的字符POSIX模式是[^[:digit:]]但是在Perl中它只表示為\D

  類似的捷徑對於空白也存在:\s[[:space:]]相對應非空白:\S對應[^[:space:]]列表A中顯示了一個取自人力資源方案的例子 列表A

  SQL> SELECT last_nameFROM employeesWHERE REGEXP_LIKE(last_name [[:space:]]);

  LAST_NAME

  

  De Haan

  SQL> SELECT last_nameFROM employeesWHERE REGEXP_LIKE (last_name \s);

  LAST_NAME

  

  De Haan

  SQL> SELECT last_nameFROM employeesWHERE REGEXP_LIKE (last_name \S)AND ROWNUM < ;

  LAST_NAME

  

  AbelAnde

  Atkinson

  Austin

  BaerBaida

  Banda

  Bates

  Bell

  Bernstein

   rows selected

  Perl通過對運算符添加(?)非貪婪型匹配運算符擴充了POSIX中的貪婪型匹配運算符例如符號^{}與至少兩個最多五個開頭的值相匹配但是解釋是貪婪的它盡可能地與最長的字符串相匹配如果列中包括了這種符號將與前五個匹配但是符號^{}?非貪婪的它與最短的字符串匹配這樣就只有兩個列表B中是一個利用REGEXP_SUBSTR的例子


   列表B

  SQL> CREATE TABLE testtab (x VARCHAR());

  Table created

  SQL> BEGIN
      FOR i IN LOOP
        INSERT INTO testtab VALUES (LPAD(i));
      END LOOP;
    END;
    /

  PL/SQL procedure successfully completed

  SQL> SELECT x FROM testtab;

  X                                                                              
                                                                     
                                                                               
                                                                              
                                                                             
                                                                            
                                                                           
                                                                          
                                                                         
                                                                        
                                                                       
                                                                      

   rows selected

  SQL> SELECT REGEXP_SUBSTR(x ^{})
    FROM testtab;

  REGEXP_SUB                                                                     
                                                                     
                                                                               
                                                                               
                                                                               
                                                                             
                                                                            
                                                                           
                                                                          
                                                                          
                                                                          
                                                                          

   rows selected

  SQL> SELECT REGEXP_SUBSTR(x ^{}?)
    FROM testtab;

  REGEXP_SUB                                                                     
                                                                     
                                                                               
                                                                               
                                                                               
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             

   rows selected 


From:http://tw.wingwit.com/Article/program/Oracle/201311/17650.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.