Oracle--萬用字元、Escape逸出字元、模糊查詢語句

標籤：項目   符號   post   匹配   article   特殊字元   插入   輸入   har   

一、萬用字元

萬用字元描述樣本
      %：匹配包含零個或更多字元的任一字元串。WHERE title LIKE ‘%computer%‘ 將尋找處於書名任意位置的包含單詞 computer 的所有書名。
      _（底線）：匹配任何單個字元。WHERE au_fname LIKE ‘_ean‘ 將尋找以 ean 結尾的所有 4 個字母的名字（Dean、Sean 等）。
      [ ]：指定範圍 ，([a-f]) 或集合 ([abcdef]) 中的任何單個字元。WHERE au_lname LIKE ‘[C-P]arsen‘ 將尋找以arsen 結尾且以介於 C 與 P 之間的任何單個字元開始的作者姓氏，例如，Carsen、Larsen、Karsen 等。
      [^]：不屬於指定範圍， ([a-f]) 或集合 ([abcdef]) 的任何單個字元。WHERE au_lname LIKE ‘de[^l]%‘ 將尋找以 de 開始且其後的字母不為 l 的所有作者的姓氏。

1、LIKE‘Mc%‘ 將搜尋以字母 Mc 開頭的所有字串（如 McBadden）。
2、LIKE‘%inger‘ 將搜尋以字母 inger 結尾的所有字串（如 Ringer、Stringer）。
3、LIKE‘%en%‘ 將搜尋在任何位置包含字母 en 的所有字串（如 Bennet、Green、McBadden）。
4、LIKE‘_heryl‘ 將搜尋以字母 heryl 結尾的所有六個字母的名稱（如 Cheryl、Sheryl）。
5、LIKE‘[CK]ars[eo]n‘ 將搜尋下列字串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
6、LIKE‘[M-Z]inger‘ 將搜尋以字串 inger 結尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer）。
7、LIKE‘M[^c]%‘ 將搜尋以字母 M 開頭，並且第二個字母不是 c 的所有名稱（如MacFeather）。

二、Escape 逸出字元

使用者輸入如果沒有任何限制的話，則必須對特殊字元進行變換。
例如：如果對單引號不進行變換，則會發生資料庫錯誤，甚至可能導致系統崩潰。
不過迴避方法卻非常簡單，只要將單引號[‘]轉換成兩個單引號[‘‘]就可以了。
 
例：SELECT * FROM TBL WHERE COL = ‘ABC‘‘DEF‘; 
 模糊查詢的語句雖然不會發生SQL錯誤，但是不進行迴避的話，則無法得到要檢索的值。
 迴避方法較單引號複雜。需要使用轉義符。將[%]轉為[/%]、[_]轉為[/_]，然後再加上[ESCAPE ‘/‘]就可以了。 
    例：SELECT * FROM TBL WHERE COL LIKE ‘ABC/%/_%‘ ESCAPE ‘/‘; 
  ※最後一個%是萬用字元。
  
如果做日文項目的話，會出現全形字元的[％]、[＿]，而這兩個全形字元同樣會作為半形萬用字元處理。所以在變換時，同時需要將全形的[％]、[＿]進行變換。 
例：SELECT * FROM TBL WHERE COL LIKE ‘ABC/%/_/％/＿%‘ ESCAPE ‘/‘; 
變換成這樣似乎結束了，可是不要忘了還有轉義符自身，萬一使用者輸入轉義符的話，
以上的處理就會發生SQL錯誤。所以也必須對轉義符進行變換。變換方法就是將[/]轉換為[//]。
例：SELECT * FROM TBL WHERE COL LIKE ‘ABC/%/_/％///＿%‘ ESCAPE ‘/‘; 
以上的操作都針對於一般的資料類型，如CHAR、VARCHAR2。
 如果出現NCHAR、NVARCHAR2的話，以上的處理就會出現ORA-01425錯誤。
 
如果改成以下寫法，則會發生ORA-01424錯誤。 
    SELECT * FROM TBL WHERE COL LIKE ‘%/＿%‘ ESCAPE TO_NCHAR(‘/‘) 
正確的寫法應該是 
    SELECT * FROM TBL WHERE COL LIKEC ‘%/＿%‘ ESCAPE TO_NCHAR(‘/‘) 

三、模糊查詢語句

 

[sql] view plain copy 

1. CREATE TABLE testsql (test_data VARCHAR2(15) NOT NULL);  
2. SELECT * FROM testsql ;  

其中包含特殊的字元分別為%,_,&，有可能包含這些字元的資料含有錯誤，或者需要尋找包含這些字元的資料。
例1、4種模糊查詢查底線
1、SQL> SELECT * FROM testsql WHERE test_data LIKE ‘sdd_%‘ ESCAPE ‘ ‘;

     其中： 逸出字元為‘ ‘(空一格);
2、SQL> SELECT * FROM testsql WHERE test_data LIKE ‘sdd/_%‘ ESCAPE ‘/‘;

    其中：逸出字元為‘/‘;

3、SQL> SELECT * FROM testsql WHERE test_data LIKE ‘sdd=_%‘ escape ‘=‘;

   其中： 逸出字元為‘=‘;
4、SQL> SELECT * FROM testsql WHERE test_data LIKE ‘sddd_%‘ escape ‘d‘;
未選定行
        逸出字元為d，沒有實現轉義功能；

同理：通過這種方法尋找含有‘%‘的所有欄位：
SQL> SELECT * FROM testsql WHERE test_data LIKE ‘%/%%‘ escape ‘/‘;
 

例2、 但是‘&‘不能通過逸出字元尋找：
SQL> SELECT * FROM testsql WHERE test_data LIKE ‘%/&%‘ escape‘/‘;                             *
   

        可以通過另外的方式進行轉義：
SQL> SELECT ASCII(‘&‘) FROM dual;
  
SQL> SELECT * FROM testsql WHERE test_data LIKE ‘%‘||chr(38)||‘%‘;
        

oracle 使用“||”進行字串串連

 例3、 ‘的轉義：
SQL> SELECT * FROM testsql WHERE test_data LIKE ‘%‘‘%‘;

例4、特殊符號“&”的插入

方法一、SQL> INSERT INTO testInsert VALUES(‘test‘||chr(38)||‘‘);

方法二、SQL>INSERT INTO testInsert VALUES(‘Tom‘ || ‘&‘ || ‘Jerry‘ )

例5、特殊符號單引號的插入

方法一、SQL>INSERT INTO testInsert VALUES(‘test‘ || ‘‘‘‘ )；

注意：這裡的‘‘‘‘四個單引號是什麼意思呢？首先第一個和最後一個都是Oracle中的字串串連符，這個沒有異議。那麼第二個‘和第三‘又表示什麼意思呢？第二個‘是一個逸出字元。第三個‘才是我們真正的內容

方法二、SQL>INSERT INTO testInsert VALUES(‘test‘‘‘ );

方法三、SQL>INSERT INTO testInsert VALUES(‘test‘|| chr(39)||‘‘);

Oracle--萬用字元、Escape逸出字元、模糊查詢語句