SQL模糊查询语句和Escape转义字符

通配符描述示例
% 包含零个或更多字符的任意字符串。 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 的所有作者的姓氏。

将通配符作为文字使用

可以将通配符模式匹配字符串用作文字字符串,方法是将通配符放在括号中。下表显示了使用 LIKE 关键字和 [ ] 通配符的示例。

符号含义
LIKE '5[%]' 5%
LIKE '[_]n' _n
LIKE '[a-cdf]' a、b、c、d 或 f
LIKE '[-acdf]' -、a、c、d 或 f
LIKE '[ [ ]' [
LIKE ']' ]
LIKE 'abc[_]d%' abc_d 和 abc_de
LIKE 'abc[def]' abcd、abce 和 abcf
SQL模糊查询,使用like比较字,加上SQL里的通配符,请参考以下: 

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 '\'; 
以上的操作都针对于一般的数据类型,如CHARVARCHAR2

如果出现
NCHARNVARCHAR2的话,以上的处理就会出现ORA-01425错误。



如果改成以下写法,则会发生
ORA-01424错误。
SELECT * FROM TBL WHERE COL LIKE '%\_%' ESCAPE TO_NCHAR('\') 
正确的写法应该是 
SELECT * FROM TBL WHERE COL LIKEC '%\_%' ESCAPE TO_NCHAR('\') 


最后要说明的是每个
like都应该写ESCAPE语句。
例:

SELECT * FROM TBL

WHERE COL1 LIKE '%\
_%' ESCAPE '\' OR COL2 LIKE '%\_%' ESCAPE '\'

SQL> select * from test;

TEST
--------------------
sdd_kk
d'd
dfsfsa
dffa%asfs
12345
1%2345
1%54321
2%54321
%%54321
A&B

已选择9行。

        其中包含特殊的字符分别为%,_,&,有可能包含这些字符的数据含有错误,或者需要查找包含这些字符的数据。


SQL> select * from test where test like 'sdd _%' escape ' ';

TEST
--------------------
sdd_kk

        转义字符为' '(空格);

SQL> select * from test where test like 'sdd\_%' escape '\';

TEST
--------------------
sdd_kk

转义字符为'\';


SQL> select * from test where test like 'sdd=_%' escape '=';

TEST
--------------------
sdd_kk

        转义字符为'=';

SQL> select * from test where test like 'sdd/_%' escape '/';

TEST
--------------------
sdd_kk

          转义字符为'/';

SQL> select * from test where test like 'sddd_%' escape 'd';

未选定行

        转义字符为d,没有实现转义功能;

SQL> select * from test where test like '%\_%' escape '\';

TEST
--------------------
sdd_kk

        查找包含所有'_'的字段。

        同理:通过这种方法查找含有'%'的所有字段:

SQL> select * from test where test like '%\%%' escape '\';

TEST
--------------------
dffa%asfs
1%2345
1%54321
2%54321
%%54321

        但是'&'不能通过转义字符查找:

SQL> select * from test where test like '%\&%' escape'\';
select * from test where test like '%\&%' escape'\'
                                   *
第 1 行出现错误:
ORA-01424: 转义符之后字符缺失或非法

        可以通过另外的方式进行转义:

SQL> select ascii('&') from dual;

ASCII('&')
----------
        38

SQL> select * from test where test like '%'||chr(38)||'%';

TEST
--------------------
A&B

           '''的转义:

SQL> select * from test where test like '%''%';

TEST
--------------------
d'd

特殊符号的数据的插入

SQL> insert into test values('test&test');
输入 test 的值: test
原值    1: insert into test values('test&test')
新值    1: insert into test values('testtest')          -虽然插入,但是数据不对。

已创建 1 行。

SQL> show define
define "&" (hex 26)
SQL> set define off
SQL> show define
define OFF
SQL> insert into test values('test&test');

已创建 1 行。

SQL> show escape
escape OFF
SQL> set escape on
SQL> show escape
escape "\" (hex 5c)
SQL> insert into test values('test\&test');

已创建 1 行。

SQL> select * from test;

TEST
--------------------
sdd_kk
d'd
dfsfsa
dffa%asfs
12345
1%2345
1%54321
2%54321
%%54321
A&B
testtest

TEST
--------------------
test&test
test&test

已选择13行。

SQL> commit;

提交完成。

SQL> select * from test;

TEST
--------------------
sdd_kk
d'd
dfsfsa
dffa%asfs
12345
1%2345
1%54321
2%54321
%%54321
A&B
testtest

TEST
--------------------
test&test
test&test

已选择13行。

SQL> insert into test values('test\%test');

已创建 1 行。

SQL> insert into test values('test\_test');

已创建 1 行。

SQL> insert into test values('test\'test);
insert into test values('test'test)
                              *
第 1 行出现错误:
ORA-00917: 缺失逗号


SQL> insert into test values('test''test');

已创建 1 行。

SQL> select * from test;

TEST
--------------------
sdd_kk
d'd
dfsfsa
dffa%asfs
12345
1%2345
1%54321
2%54321
%%54321
A&B
testtest

TEST
--------------------
test&test
test&test
test%test
test_test
test'test

已选择16行。

连接符‘||’导致了新一轮的转义:连接符号‘||’左右的单引号没有任何的关系,除非‘||’是作为字符串的一部分(这在动态SQL中很常见)。

SQL> SELECT 'ORACLE'||'''' FROM DUAL;

'ORACLE'||''
----------
ORACLE'
个人理解,'ORACLE'||'''' 后面的“''''”应该认为是一个字符串(即前后单引号,中间是“''”串,而中间又是密集单引号,因此第一个为转义功能)
SQL> SELECT 'ORACLE''''' FROM DUAL;

'ORACLE'''''
------------
ORACLE''


对于第一个,前两个单引号配对,后面四个单引号按照上面的第一条原则分配,既:SELECT 'ORACLE'||'''' FROM DUAL;
对于第二个,由于第二个单引号后面存在单引号,所以就不与第一个配对,而是充当了转义的角色。既:SELECT 'ORACLE''''' FROM DUAL;

注:本人引用了网友的资源,如侵犯了您的版权,请告知,我会立即修改指明出处

posted @ 2010-07-16 14:46  星火卓越  阅读(16075)  评论(0编辑  收藏  举报