ORACLE瀛︿範绗旇绯诲垪锛?0锛夋鍒欒〃杈惧紡


涓€銆佸厓瀛楃锛?

^	鍖归厤瀛楃涓茬殑寮€澶翠綅缃?--濡傛灉A鏄瓧绗︿覆鐨勭涓€涓瓧绗︼紝^A 鍖归厤 A
$	鍖归厤瀛楃涓茬殑鏈熬浣嶇疆	--濡傛灉B鏄瓧绗︿覆鐨勬渶鍚庝竴涓瓧绗︼紝$B 鍖归厤 B
*	鍖归厤鍓嶉潰鐨勫瓧绗?娆℃垨澶氭	--ba*rk鍙互鍖归厤 brk銆乥ark銆乥aark绛夌瓑
+	鍖归厤鍓嶉潰鐨勫瓧绗?娆℃垨澶氭	--ba+rk鍙互鍖归厤 bark銆乥aark绛夌瓑,浣嗘槸涓嶈兘鍖归厤brk锛宎鏈€灏戞湁浠ヤ竴娆°€?
?	鍖归厤鍓嶉潰鐨勫瓧绗?娆℃垨1娆?--ba?rk鍙互鍖归厤 bark銆乥rk绛夌瓑,浣嗘槸涓嶈兘鍖归厤baark銆?
.	鍖归厤闄ull浠ュ鐨勪换鎰忓崟涓瓧绗?--hob.it涓殑.鍙互鏄换鎰忕殑鍗曚釜瀛楃锛屽锛歨obsit绛夌瓑
|	鍖归厤鈥滄垨鈥?鎸囨槑涓ら」涔嬮棿鐨勪竴涓€夋嫨銆?--x|y鍙互鍖归厤x鎴栬€厃锛涗緥瀛?^([a-z]+|[0-9]+)$'琛ㄧず鎵€鏈夊皬鍐欏瓧姣嶆垨鏁板瓧缁勫悎鎴愮殑瀛楃涓层€?
\	鍙嶆枩绾垮瓧绗﹁鏄庤鍖归厤鐨勫瓧绗︽槸涓€涓壒娈婂瓧绗︺€佸父閲忔垨鑰呭悗鑰呭紩鐢ㄣ€傦紙鍚庡紩鐢ㄩ噸澶嶄笂涓€娆＄殑鍖归厤锛?-	\n 鍖归厤鎹㈣绗︼紱 \\ 鍖归厤 \ 锛? \( 鍖归厤 ( 锛? \) 鍖归厤 )      
\n	杩欐槸瀵瑰墠涓€娆″尮閰嶅懡涓殑涓€涓悗寮曠敤锛屽叾涓璶鏄竴涓鏁存暟	--(.)\1鍙互鍖归厤涓や釜杩炵画鐩稿悓鐨勯潪绌哄瓧绗︺€?.)鍙互鍖归厤闄ull浠ュ鐨勪换浣曞崟涓瓧绗︼紝鑰孿1鍒欓噸澶嶄笂涓€娆″尮閰嶇殑鍐呭锛屽嵆鍐嶆鍖归厤鐩稿悓鐨勫瓧绗︼紝鍥犳鍙互鍖归厤涓や釜杩炵画鐩稿悓鐨勯潪绌哄瓧绗?

'{n}'	鍖归厤鍓嶉潰鐨勫瓧绗︽伆濂芥槸n娆★紝鍏朵腑n鏄暣鏁?--hob{2}it鍙互鍖归厤hobbit
'{m,n}' 涓€涓簿纭湴鍑虹幇娆℃暟鑼冨洿锛宮=<鍑虹幇娆℃暟<=n锛?{m}'琛ㄧず鍑虹幇m娆★紝'{m,}'琛ㄧず鑷冲皯鍑虹幇m娆°€? 鍏朵腑n锛宮閮芥槸鏁存暟	--hob{2,3}it鍙互鍖归厤hobbit鎴栬€卙obbbit
'( )' 鍒嗙粍琛ㄨ揪寮忥紝鐪嬩綔鍗曚釜瀛愯〃杈惧紡銆傛爣璁颁竴涓瓙琛ㄨ揪寮忕殑寮€濮嬪拰缁撴潫浣嶇疆銆?-濡傦細aaa(x|y)鍙互鍖归厤aaax鎴栬€卆aay銆?
'[]'  鏂规嫭鍙疯〃绀烘寚瀹氫竴涓尮閰嶅垪琛紝璇ュ垪琛ㄥ尮閰嶅垪琛ㄤ腑鏄剧ず鐨勪换浣曡〃杈惧紡銆?
'[^]' 鍚屼笂闈㈢浉鍙嶏紝闈炲尮閰嶅垪琛ㄨ〃杈惧紡銆?
'[abc]'	鍙互鍖归厤abc涓殑浠讳綍鍗曚釜瀛楃	--hello[abc]鍙互鍖归厤helloa,hellob,helloc
'[a-z]'	鍙互鍖归厤鎸囧畾鑼冨洿鍐呯殑浠讳綍鍗曚釜瀛楃	--hell[a-z]鍙互鍖归厤hello鎴栬€卙ellz
'[::]'	鎸囧畾涓€涓瓧绗︾被锛屽彲浠ュ尮閰嶈绫讳腑鐨勪换浣曞瓧绗?
      [:alnum:] 鎵€鏈夌殑瀛楁瘝鍜屾暟瀛楀瓧绗?鍙互鍖归厤瀛楃0-9銆丄-Z銆乤-z    
      [:alpha:] 鎵€鏈夌殑瀛楁瘝瀛楃,鍙互鍖归厤瀛楃A-Z銆乤-z  
      [:blank:] 鍙互鍖归厤绌烘牸鎴杢ab閿?  
      [:cntrl:] 鎵€鏈夌殑鎺у埗瀛楃(涓嶄細鎵撳嵃鍑烘潵)   
      [:digit:] 鎵€鏈夌殑鏁板瓧,鍙互鍖归厤鏁板瓧0-9 
      [:graph:] 鍙互鍖归厤闈炵┖瀛楃,鎵€鏈夌殑[:punct:]銆乕:upper:]銆乕:lower:]鍜孾:digit:]瀛楃   
      [:lower:] 鍙互鍖归厤灏忓啓瀛楁瘝a-z 
      [:print:] 涓嶽:graph:]绫讳技锛屼笉鍚屼箣澶勫湪浜嶽:print:]鍖呮嫭绌烘牸瀛楃   
      [:punct:] 鎵€鏈夌殑鏍囩偣绗﹀彿,鍙互鍖归厤鏍囩偣绗﹀彿.,""""绛夌瓑   
      [:space:] 鎵€鏈夌殑绌洪殧瀛楃(涓嶄細鎵撳嵃鍑烘潵)   
      [:upper:] 鍙互鍖归厤澶у啓瀛楁瘝A-Z   
      [:xdigit:] 鎵€鏈夋湁鏁堢殑鍗佸叚杩涘埗瀛楃锛岀浉褰撲簬[0-9a-fA-F] 
      
 
浜屻€佸悇绉嶆搷浣滅鐨勮繍绠椾紭鍏堢骇锛?
\杞箟绗? 
(), (?:), (?=), [] 鍦嗘嫭鍙峰拰鏂规嫭鍙? 
*, +, ?, {n}, {n,}, {n,m} 闄愬畾绗? 
^, $, anymetacharacter 浣嶇疆鍜岄『搴? 

涓夈€丱racle涓敮鎸佺殑姝ｅ垯琛ㄨ揪寮忓嚱鏁帮細
        锛?锛塕EGEXP_LIKE锛?  涓嶭IKE 鐨勫姛鑳界浉浼? 
        锛?锛塕EGEXP_INSTR锛? 涓嶪NSTR 鐨勫姛鑳界浉浼? 
        锛?锛塕EGEXP_SUBSTR锛?涓嶴UBSTR 鐨勫姛鑳界浉浼? 
        锛?锛塕EGEXP_REPLACE锛氫笌REPLACE 鐨勫姛鑳界浉浼? 
        锛?锛塕EGEXP_COUNT锛? 涓嶭ENGTH 鐨勫姛鑳界浉浼? 

聽

聽

锛?锛塕EGEXP_LIKE 鍑芥暟  

璇硶锛?
--REGEXP_LIKE 涓?LIKE 鍑芥暟鐩稿悓锛岃繑鍥炲竷灏旂被鍨?
REGEXP_LIKE(srcstr, pattern [,match_option]) 

娉細 
   srcstr          婧愬瓧绗︿覆 
   pattern         姝ｅ垯琛ㄨ揪寮忔牱寮? 
   match_option    鍖归厤閫夐」锛堝尯鍒嗗ぇ灏忓啓锛?
   
鍏朵腑match_option鐨勫彇鍊艰寖鍥村涓嬶細
    i锛氬ぇ灏忓啓涓嶆晱鎰燂紱
    c锛氬ぇ灏忓啓鏁忔劅锛?
    n锛氱偣鍙?. 涓嶅尮閰嶆崲琛岀鍙凤紱
    m锛氬琛屾ā寮忥紱
    x锛氭墿灞曟ā寮忥紝蹇界暐姝ｅ垯琛ㄨ揪寮忎腑鐨勭┖鐧藉瓧绗︺€?
 
create table B
(
  id   NUMBER,
  name VARCHAR2(100)
);

--鏁版嵁鎻掑叆    
INSERT INTO B VALUES ('1', '20140107');
INSERT INTO B VALUES ('2', '2014-01-07');
INSERT INTO B VALUES ('3', '2014-01-07 21:15:27');
INSERT INTO B VALUES ('4', '2014骞?鏈?鏃ユ槦鏈熶簩');
INSERT INTO B VALUES ('5', 'http://weishaoxiang.iteye.com');
INSERT INTO B VALUES ('6', 'weishaoxiang');
INSERT INTO B VALUES ('7', '鍚夌ゥ濡傛剰');
INSERT INTO B VALUES ('8', '088-12345678');
INSERT INTO B VALUES ('9', 'weishaoxiang@163.com');
INSERT INTO B VALUES ('10', '1881234567');
INSERT INTO B VALUES ('11', 'M00588');
INSERT INTO B VALUES ('12', '297187');
INSERT INTO B VALUES ('13', '192.168.0.102');
INSERT INTO B VALUES ('14', 'F5-DE-F3-E7-B9-99');
INSERT INTO B VALUES ('15', 'who are you');
INSERT INTO B VALUES ('16', '渚?鏅? 姝?  鍠?鍘?鏅? 鎮诧紝绌哄湪 浜洪枔  璧颁竴鍥炪€?);
INSERT INTO B VALUES ('17', '2M00K88');
INSERT INTO B VALUES ('18', 'aaBB');
INSERT INTO B VALUES ('19', 'aaBBff');
INSERT INTO B VALUES ('20', 'GGpp');
INSERT INTO B VALUES ('21', 'LLOVEYOU');
INSERT INTO B VALUES ('22', '^_^ >_< $@$ ');

--鏌ヨ浠?20寮€澶?07缁撴潫鐨勮褰曞苟涓旈暱搴︽槸 8浣?   
SELECT * FROM B WHERE NAME LIKE '20____07';
SELECT * FROM B WHERE REGEXP_L