博客

oracle 特殊字符 转义

  • Post author:
  • Post category:其他


、oracle 特殊字符 转义

关键词： oracle    转义

环境：oracle 9i plsql

在plsql里边执行:

update userinfo set pageurl=’myjsp?page=1&pagesize=10′ where id=’test’

这条sql语句往数据库的pageurl字段放进去了一个url地址，但是执行的时候却并非那么理想，因为这其中有一个oracle的特殊字符，需要进行转义，那就是字符’&’.

怎么处理上例中的特殊字符？

两个办法：

1) update userinfo set pageurl=’myjsp?page=1’||’&’||’pagesize=10′ where id=’test’

2) update userinfo set pageurl=’myjsp?page=1’||chr(38)||’pagesize=10′ where id=’test’

其中||是连字符, chr(38)跟ASCII的字符转码是一致的。

plsql中还可以set define off来关闭特殊字符，还可以用show define来查看有些特殊定义的字符。


2、oracle 中如何转义 特殊字符

问 : 如何转义 下划线 _

select * from ng_values where name like ‘lady_%’

jieguo 结果显示 lady_test,lady_test,lady1

正确结果应该是:lady_test,lady_test

不包括 lady1

请各位给出转义方法,3ks

answer:

select … from … where … like ‘/_%’ escape ‘/’;


3、

insert into t(col) values(chr(ascii(‘&’))) ；

(方法一)

例:插入特殊字元’&’

SQL> SHOW DEFINE

define “&” (hex 26) ? <— DEFINE的default值是 ‘&’

SQL> SET DEFINE OFF

SQL> SHOW DEFINE

define OFF

SQL> INSERT INTO <table_name> VALUES (‘AT&T’);

/

1 row created

(方法二)

SQL> SHOW ESCAPE

escape OFF <— ESCAPE的default值是 OFF

SQL> SET ESCAPE ON

SQL> SHOW ESCAPE

escape “/” (hex 5c)

SQL> INSERT INTO temp_table VALUES (‘select * from emp where ename = /&1’);

1 row created.

；

几个测试方法：

SELECT ‘myjsp?page=1&pagesize=10’ FROM dual;

SELECT ‘myjsp?page=1&pagesize=10’ FROM dual;

今天要在字段里查找’%’，涉及到oracle的转义字符，现总结如下：


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;