Shulou(Shulou.com)05/31 Report--

This article shows you how to escape special characters in oracle. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Key words: oracle escape

Environment: oracle 9i plsql

Execute in plsql:

Update userinfo set pageurl='myjsp?page=1&pagesize=10' where id='test'

This sql statement puts an url address in the pageurl field of the database, but it is not ideal for execution because there is a special character of oracle that needs to be escaped, that is, the character'&'.

What do I do with the special characters in the above example?

There are two ways:

1) update userinfo setpageurl='myjsp?page=1' | |'&'| | 'pagesize=10' where id='test'

2) update userinfo setpageurl='myjsp?page=1' | | chr (38) | | 'pagesize=10' where id='test'

Where | | is a hyphen, and the character transcoding of chr (38) is consistent with that of ASCII.

You can also use set define off to turn off special characters in plsql, and you can use showdefine to view some specially defined characters.

2. How to escape special characters in oracle

Q: how to escape an underscore _

Select * from ng_values wherename like 'lady_%'

Jieguo results show lady_test,lady_test,lady1

The correct result should be: lady_test,lady_test

Does not include lady1

Please give the escape method, 3ks.

Answer:

Select... From... Where... like'/ _% 'escape' /'

3 、

Insert into t (col) values (chr (ascii (&)

(method 1)

Example: insert a special character'&'

SQL > SHOW DEFINE

Define "&" (hex 26)? SET DEFINE OFF

SQL > SHOW DEFINE

Define OFF

SQL > INSERT INTO VALUES ('AT&T')

/

1 row created

(method 2)

SQL > SHOW ESCAPE

Escape OFF SET ESCAPE ON

SQL > SHOW ESCAPE

Escape "\" (hex 5c)

SQL > INSERT INTO temp_table VALUES ('select * from emp where ename =\ & 1')

1 row created.

Several test methods:

SELECT 'myjsp?page=1&pagesize=10' FROM dual

SELECT 'myjsp?page=1&pagesize=10' FROM dual

SQL > create table a (b varchar2 (10))

Table created

SQL > insert into a values ('_ a')

1 row inserted

SQL > select B from A where instr (bmena`) > 0

B

-

_ a

Select B from A where b like'%\ _ a% 'escape'\'

Start the escape character first

Set escape on

Select B from A where b like'%\ _ a% 'escape'\'

Select B from A where b like'%\ _ a% 'escape'\'

The above is how to escape special characters in oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.