文章目录
- 字符串like匹配遇到关键字,字符转义问题
- 造测试数据
- 转义字符例子
- 查询包含语法关键字的字段值
- 字符拼接问题
字符串like匹配遇到关键字,字符转义问题
在使用like匹配字符串的时候,如果字符串中有%、下划线、单引号等sql语法中保留字符串,这时候需要转义,mysql中保持和linux或某些编程语言中一致,使用反斜杠“\”来解决,Oracle中则稍显麻烦。
前面说过,在字符串拼接时遇到关键字可用quote(q)方法来搞定,但在where条件的like匹配中,就需要使用escape来搞定。
escape用法说明:
1)使用 ESCAPE 关键字定义转义符。在模式中,当转义符置于通配符之前时,该通配符就解释为普通字符。
2)ESCAPE 'escape_character’允许在字符串中搜索通配符而不是将其作为通配符使用。escape_character 是放在通配符前表示此特殊用途的字符。
造测试数据
set define off
create table test (c_text varchar2(20));
insert into test(c_text) values ('sdd_kk');
insert into test(c_text) values ('d''d');
insert into test(c_text) values ('dfsfsa');
insert into test(c_text) values ('dffa%asfs');
insert into test(c_text) values ('12345');
insert into test(c_text) values ('1%2345');
insert into test(c_text) values ('1%54321');
insert into test(c_text) values ('2%54321');
insert into test(c_text) values ('%%54321');
insert into test(c_text) values ('A&B');
commit;
set define on
输出结果:
SYS@orcl11g>select * from test;
C_TEXT
--------------------
sdd_kk
d'd
dfsfsa
dffa%asfs
12345
1%2345
1%54321
2%54321
%%54321
A&B
10 rows selected.
- 如何插入带单引号的值,比如:d’d
oracle中使用insert into test(c_text) values (‘d’‘d’);
mysql中使用insert into test(c_text) values (‘d\d’);
- 如何插入“&”这个字符,比如:A&B
方法一: 通用,推荐
SYS@orcl11g>select ascii('&') from dual;
ASCII('&')
----------
38
SYS@orcl11g>insert into test(c_text) values (chr(38));
1 row created.
SYS@orcl11g>select * from test where c_text like '%'||chr(38)||'%';
C_TEXT
--------------------
A&B
&
方法二: 在sqlplus中关闭“&”字符特殊含义的定义
SQL> show define
define "&" (hex 26)
SQL> set define off
SQL> show define
define OFF
SQL> insert into test values('test&test');
转义字符例子
- 转义字符为’ '(空格);
SYS@orcl11g>select * from test where c_text like 'sdd _%' escape ' ';
C_TEXT
--------------------
sdd_kk
- 转义字符为’’
SYS@orcl11g>select * from test where c_text like 'sdd\_%' escape '\';
C_TEXT
--------------------
sdd_kk
- 转义字符为’=’
SYS@orcl11g> select * from test where c_text like 'sdd=_%' escape '=';
C_TEXT
--------------------
sdd_kk
- 转义字符为’/’
SYS@orcl11g> select * from test where c_text like 'sdd/_%' escape '/';
C_TEXT
--------------------
sdd_kk
- 转义字符为d,没有实现转义功能,其他不包含在条件中的字母可以
SYS@orcl11g>select * from test where c_text like 'sddd_%' escape 'd';
no rows selected
SYS@orcl11g>select * from test where c_text like 'sddc_%' escape 'c';
C_TEXT
--------------------
sdd_kk
SYS@orcl11g>select * from test where c_text like 'sdds_%' escape 's';
select * from test where c_text like 'sdds_%' escape 's'
*
ERROR at line 1:
ORA-01424: missing or illegal character following the escape character
查询包含语法关键字的字段值
- 查找包含所有’_'的字段
SYS@orcl11g>select * from test where c_text like '%\_%' escape '\';
C_TEXT
--------------------
sdd_kk
- 查找含有’%'的所有值
SYS@orcl11g>select * from test where c_text like '%\%%' escape '\';
C_TEXT
--------------------
dffa%asfs
1%2345
1%54321
2%54321
%%54321
- 查询包含“&”字符的内容
如果直接使用上面介绍的方法会报错:
SYS@orcl11g>select * from test where c_text like '%\&%' escape'\';
select * from test where c_text like '%\&%' escape'\'
*
ERROR at line 1:
ORA-01424: missing or illegal character following the escape character
使用对应的ASCII码去匹配才可以:
SYS@orcl11g> select * from test where c_text like '%'||chr(38)||'%';
C_TEXT
--------------------
A&B
- 查找含有单引号的值
SYS@orcl11g>select * from test where c_text like '%''%';
C_TEXT
--------------------
d'd
- 有关单引号的特殊情况
情况一:
SYS@orcl11g>SELECT 'ORACLE'||'''' FROM DUAL;
'ORACLE
-------
ORACLE'
情况二:
SYS@orcl11g>SELECT 'ORACLE''''' FROM DUAL;
'ORACLE'
--------
ORACLE''
对于情况一,前两个单引号配对,后面四个单引号表示单引号这个字符串。
对于情况二,总共6个单引号,第1、6单引号表示字符串表达用的开头结尾单引号,第2个单引号表示转义符号(因转义牺牲掉),第3个单引号表达出原值(保留),第4个单引号也表示转义,第5个单引号表达出原值。所以,最后结果出现两个单引号。
对比’ORACLE’||’’’'的情况,Oracle字符串在使用||拼接的时候,||前后都是一个字符串,写sql的时候要特别注意。
字符拼接问题
方法一:推荐
一般在拼接字符串时,‘A’||'B’简单情况是可以的,但是如果包含单引号这样的关键字就会报语法错误了,使用Quote(q)语法就可以搞定,好处如下:
a.指定自己的引号分隔符
b.可选择任意方便的分隔符,单字节或多字节,或者下列符号: [ ], { }, ( ), < >。
c.增加可读性和可用性
举例:
SQL> select department_name || q'[, it's assigned Manager Id: ]' || manager_id as "Department and Manager" from departments;
Department and Manager
-----------------------------------------------------------------------
Administration, it's assigned Manager Id: 200
Marketing, it's assigned Manager Id: 201
Purchasing, it's assigned Manager Id: 114
方法二: 处理单引号
由于单引号是匹配字符串的关键字符,所以oracle中使用两个单引号来表示。字符串两端又需要单引号,最终,为了输入一个单引号,需要4个单引号来完成,即:’’’’ ,这也就是为啥使用quote(q)方便的原因。
SYS@orcl11g>select '''' as danyinhao from dual;
D
-
'