oraclesql正則

1. oracle sql 正則regexp_like 如何包含特定字元串的問題

php">^(data1|data2|,)+$'

最好復制段匹配錯誤的數據上來，不然天曉得題主是語法用錯了，還是從開始思路都錯了

2. Oracle正則表達式

Oracle正則表達式的應用在oracle里正則表達式有四個函數可用，分別是regexp_like、regexp_substr、regexp_instr 和regexp_replace。這里在我們oracle 10g里靈活應用。 先來簡單介紹一下正則表達式的內容，正則表達式是做為快速查詢的文本內容的，在linux應用比較多，首先，行的起始與結束 「^」這個字元是表示只查找行首的內容。「$」這個字元只查找行末的內容。接下來是「^」還可以做為一個排除字元來使用。還是使用例子來做一個演示比較明了一下。 這里我使用regexp_like這個函數來做，這樣可以我們平時會使用的比較多。select * from test_tablewhere regexp_like(field_1,'^1234')這個就是表示是以1234打頭的字元串是不是有匹配的。這里和like的方式是一樣的。 select * from test_tablewhere regexp_like(field_1,'^[12]234')這里多了一個[]這里做一個獨立字元，這里表示是以1或2開始，並且接著是234這個里的字元就會是匹配的。 select * from test_tablewhere regexp_like(field_1,'^(歐陽|李)小二')這里我們就可以表達，這個查詢一個姓是歐陽或李的，名字叫小二的字元串。這里多了一個（）這個是做一個為字元串的方式來寫的與[]剛好是對應。這里還有一個「|」來表示或的意思。 select * from test_tablewhere regexp_like(field_1,'^李[小]*二')這里我們就可以查詢李小二或是李二，再或者是李小小二，都可以，這里我們需要講一下是[]後面帶了一個*，這個是表示0~無窮大 字元去匹配。這個[]我們還可以添加一個「+」來表示1~無窮大的字元去匹配，也可以更加精準一些，在[]後面{1,3}這里就是表示1個到3個相同字元的匹配。還有一個「?」來說表示1或是0個。 select * from test_tablewhere regexp_like(field_1,'李[^小]二')這里我們可以查詢到姓李的，但是第二字不是「小」這個字。 select * from test_tablewhere regexp_like(field_1,'[0-9]')這里是表示我們查詢字元串含有0-9的數字的字元串。 select * from test_tablewhere regexp_like(field_1,'[a-z]')這里是表示我們查詢字元串含有a-z的小寫字母的字元串。 select * from test_tablewhere regexp_like(field_1,'[A-z]')這里是表示我們查詢字元串含有A-z的所有字母的字元串。 select * from test_tablewhere regexp_like(name,'[[:alpha:]]')這里是表示查詢匹配任意字母，也包括中文字 select * from test_tablewhere regexp_like(name,'[[:alnum:]]')這里是表示查詢匹配任意字母和數字 select * from test_tablewhere regexp_like(name,'[[:digit:]]')這里是表示查詢匹配任意數字 Select * from test_tableWhere regexp_like(name,』of』,』i』)這里就是of不區分大小寫 Select * from test_tableWhere regexp_like(name,』^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$』)這樣我們可以查詢是不是ip格式 接下來介紹一下regexp_substr這個也是一個非常實用的一個函數 REGEXP_SUBSTR與SUBSTR函數相同，返回截取的子字元串 REGEXP_SUBSTR(srcstr, pattern [, position [, occurrence [, match_option]]]) 註： srcstr 源字元串 pattern 正則表達式樣式 position 開始匹配字元位置 occurrence 匹配出現次數 match_option 匹配選項（區分大小寫） SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+') FROM al; Output: 1PSN [[:alnum:]]+ 表示匹配1個或者多個字母或數字字元 SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+', 1, 2) FROM al; Output: 231 與上面一個例子相比，多了兩個參數1 表示從源字元串的第一個字元開始查找匹配2 表示第2次匹配到的字元串（默認值是「1」，如上例） select regexp_substr('@@/231_3253/ABc','@*[[:alnum:]]+') from al; Output: 231 @* 表示匹配0個或者多個@ [[:alnum:]]+ 表示匹配1個或者多個字母或數字字元注意：需要區別「+」和「*」的區別 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]*') from al; Output: @@+ 表示匹配1個或者多個@ [[:alnum:]]* 表示匹配0個或者多個字母或數字字元 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]+') from al; Output: Null @+ 表示匹配1個或者多個@ [[:alnum:]]+ 表示匹配1個或者多個字母或數字字元 select regexp_substr('@1PSN/231_3253/ABc125','[[:digit:]]+$') from al; Output: 125 [[:digit:]]+$ 表示匹配1個或者多個數字結尾的字元 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]+') from al; Output: Null @+ 表示匹配1個或者多個@ [[:alnum:]]+ 表示匹配1個或者多個字母或數字字元 select regexp_substr('@1PSN/231_3253/ABc125','[[:digit:]]+$') from al; Output: 125 [[:digit:]]+$ 表示匹配1個或者多個數字結尾的字元 select regexp_substr('@1PSN/231_3253/ABc','[^[:digit:]]+$') from al; Output: /ABc [^[:digit:]]+$ 表示匹配1個或者多個不是數字結尾的字元 select regexp_substr('[email protected]','[^@]+') from al; Output: Tom_Kyte [^@]+ 表示匹配1個或者多個不是「@」的字元 select regexp_substr('1PSN/231_3253/ABc','[[:alnum:]]*',1,2) from al; Output: Null [[:alnum:]]* 表示匹配0個或者多個字母或者數字字元註：因為是匹配0個或者多個，所以這里第2次匹配的是「/」（匹配了0次），而不是「231」，所以結果是「Null」 這里我們有時候會查詢字元串里asdfafd<main>dafda 這里我們要取出<main>這個字元串Select regexp_substr('asdfafd<main>dafda','<[^>]+>') from alOutput: <main> 這里我們在<>中間去一個^>這樣在匹配<之後，在向後查詢的時候確保在匹配到>之前不再在有>,不然的話就要有可以出錯的情況。 Select regexp_substr('asdfafd<main>da>fda','<[^<]+>') from alOutput: <main>da>在這個例子中，我們在<main>之後還在da>，這樣的話，如果我們沒有添加^>，正則表達式就會向後繼續去匹配，直到最後一個>為至，這樣就會出現偏差 這個通常用來實現字元串的列傳行select regexp_substr('123;234;345;456;567;678;789','[^;]+',1,rownum) from alconnect by rownum <= length('123;234;345;456;567;678;789') - length(replace('123;234;345;456;567;678;789',';'))+1這里length這里操作是先得到有多少個「;」，再通過 connect by rownum方式來做一行成多行的操作，在變成多行之後，可以通過regexp_substr來取字元串的操作 接著上一個例子a,b,c,d,e,d,f,a,n這樣的一個字元串，我們現在要把字元串里一些重復去掉，這樣的話結果是a,b,c,d,e,f,n去掉了d與a的兩個字元串select wm_concat(new_row) from (select distinct regexp_substr('a,b,c,d,e,d,f,a,n','[^,]+',1,rownum) new_row from alconnect by rownum<=length('a,b,c,d,e,d,f,a,n')-length(replace('a,b,c,d,e,d,f,a,n',',')))通過轉成多行的，再用distinct 去掉重復，然後我們再通過wm_concat來字元串合並來完成。 再來一個ip格式轉換的例子吧，我們一般的IP的格式是12.19.168.27現在要不足3位的補足前面為0，結果是012.019.168.027select wm_concat(new_value) from (select lpad(regexp_substr('12.19.168.27','[^.]+',1,rownum) ,3,'0') new_value,rownumfrom alconnect by rownum<5order by rownum) 來一個驗證IP是數字是否正確select count(*) from(select lpad(regexp_substr('12.19.168.27','[^.]+',1,rownum) ,3,'0') new_value,rownumfrom alconnect by rownum<5)where new_value>=0 and new_value<256having count(*) =4 來一個IP字元串格式轉換成數字型IPselect sum(new_value*power(256,4-rm)) from (select regexp_substr('12.19.168.27','[^.]+',1,rownum) new_value,rownum rm from alconnect by rownum<=4) 接下來介紹一個regexp_instr函數 REGEXP_INSTR 函數使用正則表達式返回搜索模式的起點和終點。REGEXP_INSTR 的語法如下所示。REGEXP_INSTR 返回一個整數，指出搜索模式的開始或結束的位置，如果沒有發現匹配的值，則返回0。 語法： 2.REGEXP_INSTR與INSTR函數相同，返回字元串位置 REGEXP_INSTR(srcstr, pattern [, position [, occurrence [, return_option [,match_option]]]]) 與REGEXP_SUBSTR一樣，它也有變數pattern、position(開始位置)、occurrence 和match_parameter；這里主要介紹一下新參數return_option 的作用，它允許用戶告訴Oracle，模式出現的時候，要返回什麼內容。 Select regexp_instr('asdfafd<main>da>fda','sd') from alOutput:2這里去查詢sd的位置，這個和instr是在相同的 Select regexp_instr('asdfafd<main>da>fda','da',1,2) from al這里是查詢da第二出現的位置 還有我們經常會遇到一種情況是，查詢某個欄位，如果是等於「上海」或「北京」或者我們溫州就寫成大城市，其它的寫成小城市，我們一般會考慮使用decode這種方式 Select decode('上海','上海','大城市','北京' ,'大城市' ,'溫州' ,'大城市','小城市') from al只有兩個我們可能覺的sql也不是很冗長，如果有四五個的話，就有點長了，這里使用regexp_instr就可以很多的去操作 Select decode (regexp_instr('北京','^(上海|北京|溫州)'),0,'小城市', '大城市') from al通過regexp_instr不匹配時為0的條件，這樣就可以完成了 最後一個函數regexp_replaceREGEXP_REPLACE 函數是用另外一個值來替代串中的某個值。例如，可以用一個匹配數字來替代字母的每一次出現。REGEXP_REPLACE的格式如下所示 語法： 4.REGEXP_REPLACE與REPLACE函數相同，替換原字元串中的字元內容 REGEXP_REPLACE(srcstr, pattern [,replacestr [, position [, occurrence [,match_option]]]]) 這個替換函數還是一個非常好用的。如我們在有一個字元串adfadfa (main) next 現在我們要把()替換成<>，這里我們可能想用replace就可以搞定了，但是我們現在做的是(之後必須有)這樣的()我們才替換把<>.select regexp_replace('adfadfa (main) next ','(\()([^\)]*)(\))','<\2>') from aloutput: adfadfa <main> next這里還是一個\做為轉義字元。 再來一個ip格式轉換的例子吧，我們一般的IP的格式是12.19.168.27現在要不足3位的補足前面為0，結果是012.019.168.027select regexp_replace(regexp_replace('12.19.168.27','([0-9]{1,3}).([0-9]{1,3}).([0-9]{1,3}).([0-9]{1,3})','00\1.00\2.00\3.00\4') ,'([0-9]*)([0-9]{3}\.)([0-9]*)([0-9]{3}\.)([0-9]*)([0-9]{3}\.)([0-9]*)([0-9]{3}$)','\2\4\6\8')from aloutput: 012.019.168.027這里我分成兩步來操作，regexp_replace('12.19.168.27','([0-9]{1,3}).([0-9]{1,3}).([0-9]{1,3}).([0-9]{1,3})','00\1.00\2.00\3.00\4')我首先讓每個小字元串做添加0，這樣每個字元串都會大於3，再'([0-9]*)([0-9]{3}\.)([0-9]*)([0-9]{3}\.)([0-9]*)([0-9]{3}\.)([0-9]*)([0-9]{3}$)','\2\4\6\8')這整個字元串分成8段，這樣我們只要2、4、6、8這四個段就可以了。 下面一個例子中，在每兩個字元之間插入一個空格符SELECT regexp_replace('YAHOO', '(.)', '\1 ') AS output FROM al; Output: Y A H O O這個用一個循環的方式去操作，還蠻很好的。 select regexp_replace(regexp_replace('12.19.168.27','([^.]+)','00\1') ,'([^.]*)([^.]{3})','\2')from al接著剛才那個，我們可以把replace循環替換的方式來操作。

3. oracle SQL 正則表達式

用 Oracle Database 10g 使用正規表達式
您可以使用最新引進的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTR、REGEXP_SUBSTR 以及 REGEXP_REPLACE 函數來發揮正規表達式的作用。您將體會到這個新的功能如何對 LIKE 操作符和 INSTR、SUBSTR 和 REPLACE 函數進行了補充。實際上，它們類似於已有的操作符，但現在增加了強大的模式匹配功能。被搜索的數據可以是簡單的字元串或是存儲在資料庫字元列中的大量文本。正規表達式讓您能夠以一種您以前從未想過的方式來搜索、替換和驗證數據，並提供高度的靈活性。
正規表達式的基本例子
在使用這個新功能之前，您需要了解一些元字元的含義。句號 (.) 匹配一個正規表達式中的任意字元（除了換行符）。例如，正規表達式 a.b 匹配的字元串中首先包含字母 a，接著是其它任意單個字元（除了換行符），再接著是字母 b。字元串 axb、xaybx 和 abba 都與之匹配，因為在字元串中隱藏了這種模式。如果您想要精確地匹配以 a 開頭和以 b 結尾的一條三個字母的字元串，則您必須對正規表達式進行定位。脫字元號 (^) 元字元指示一行的開始，而美元符號 ($) 指示一行的結尾（參見表1：附表見第4頁）。因此， 正規表達式 ^a.b$ 匹配字元串 aab、abb 或 axb。將這種方式與 LIKE 操作符提供的類似的模式匹配 a_b 相比較，其中 (_) 是單字元通配符。
默認情況下，一個正規表達式中的一個單獨的字元或字元列表只匹配一次。為了指示在一個正規表達式中多次出現的一個字元，您可以使用一個量詞，它也被稱為重復操作符。.如果您想要得到從字母 a 開始並以字母 b 結束的匹配模式，則您的正規表達式看起來像這樣：^a.*b$。* 元字元重復前面的元字元 (.) 指示的匹配零次、一次或更多次。LIKE 操作符的等價的模式是 a%b，其中用百分號 (%) 來指示任意字元出現零次、一次或多次。
表 2 給出了重復操作符的完整列表。注意它包含了特殊的重復選項，它們實現了比現有的 LIKE 通配符更大的靈活性。如果您用圓括弧括住一個表達式，這將有效地創建一個可以重復一定次數的子表達式。例如，正規表達式 b(an)*a 匹配 ba、bana、banana、yourbananasplit 等。僅供參考！

4. oracle函數中正則表達式 是什麼意思

ORACLE 中的支持正則表達式的函數主要有下面四個：
1，REGEXP_LIKE ：與 LIKE的功能相似
2，REGEXP_INSTR ：與INSTR的功能相似
3，REGEXP_SUBSTR ：與SUBSTR 的功能相似
4，REGEXP_REPLACE ：與REPLACE的功能相似
它們在用法上與Oracle SQL 函數 LIKE、INSTR、SUBSTR 和REPLACE 用法相
同.

5. Oracle SQL 正則表達式求助

SELECT city_code FROM A
WHERE REGEXP_LIKE(city_code, '\d+')
AND
city_code <> '5Y44' AND city_code <> '0HJ7'
UNION
SELECT city_code FROM A
WHERE city_code = 'PLT'