Oracle特殊符號的模糊查詢

oracle中帶有特殊符號的模糊查詢

首先講一下Oracle模糊查詢
Oracle模糊查詢可使用的萬用字元，Oralce中SQL語句提供了四種匹配模式：
%   零或者多個字元
_    單一任何字元（下劃線）
\     特殊字元
[]     在某一範圍內的字元，如[0-9]或者[aeth]  
[^]    不在某範圍內的字元，如[^0-9]或者[^aeth]
後兩種， 需要Oracle 10g以上使用支援like的正則regexp_like

[ ]：表示括號內所列字元中的一個（類似正規表示式）。
指定一個字元、字串或範圍，要求所匹配物件為它們中的任一個
如 [ ] 內有一系列字元（01234、abcde之類的）則可略寫為“0-4”、“a-e”

[^ ] ：表示不在括號所列之內的單個字元。其取值和 [] 相同，但它要求所匹配物件為指定字元以外的任一個字元。

注：oracle like 不支援正則，你可以使用支援like的正則regexp_like
select * from table_name where regexp_like(name,'[張李王]三');

由於萬用字元的緣故，導致我們如果想要查詢包括這些特殊字元的內容“%”、“_”、“[”的語句無法正常實現

oracle中的模糊查詢，帶有特殊符號
用like語句查詢的話可以，但是有特殊符號的話不能識別，如：#，%，_ 等

第一種方法：
可以用 regexp_like(欄位名 ,'帶有特殊符號的模糊字') 這個查詢
例如：
select * from user_souce t
where to_char(t.us_time, 'yyyymmdd') = '20120615' and regexp_like(t.us_from, 'hello[Mr.hailey]');

 

第二種方法：轉義特殊字元，轉義符可以自己定義，用escape '轉義符'定義即可
例如：
-- 查詢所有包含'_'的
select * from emp where ename like '%?_%' escape '?';  --轉義符為問號?
select * from emp where ename like '%/_' escape '/';   --轉義符為斜槓/
--查詢所有以_結尾的
select * from emp where ename like '%\_' escape '\'; --轉義符為反斜槓\

 

同理，通過這種方法查詢含有'%'的所有欄位：
select * from emp where ename like '%\%%' escape '\';

 

但是'&'不能通過轉義字元查詢
如果按上面的寫法，
select * from emp where ename like '%\&' escape '\';  
會提示：ORA-01424: 轉義符之後字元缺失或非法
可以通過另外的方式進行轉義：
select ascii('&') from dual;
ASCII('&')
----------
        38
select * from emp where ename like '%' || chr(38) || '%';

 

在oracle中chr()函式和ascii()是一對反函式
chr()函式將ASCII碼轉換為字元:ASCII碼 --&gt 字元
ascii('字元')函式獲得該字元的ascii碼值，將字元轉換為ASCII碼:字元 --&gt ASCII碼

 

但是，注意%不能用這種方法轉義（%號的ascii碼為37）因為轉出來還是一個%號，還是會當做萬用字元找出所有的資料

 

--單引號的轉義
如果按上面的寫法，
select * from emp where ename like '%\'%'  escape '\';
會提示：ORA-01756:引號內的字串沒有正確結束，因為不能正確識別結束
--查詢所有含有單引號的名字
select * from emp where ename like '%''%';

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-769295/，如需轉載，請註明出處，否則將追究法律責任。