Oracle special character escape

1. Oracle special character escape Keywords: Oracle escape environment: Oracle 9i PLSQL execute in PLSQL: Update userinfo set pageurl = 'myjsp? Page = 1 & pagesize = 10 'where id = 'test' This SQL statement adds a URL address to the pageurl field of the database, but it is not ideal during execution because it contains a special character of Oracle and needs to be escaped, that is the character '&'. how to handle the special characters in the previous example? Two methods: 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' | it is a hyphen, CHR (38) it is consistent with ASCII character transcoding. In PLSQL, you can also set define off to disable special characters. You can also use show define to view some special characters. 2. How to escape special characters in Oracle Q: How to escape underline _ select * From ng_values where name like 'Lady _ % 'the result of jieguo shows lady_test, lady_test, and the correct result of lady1 should be: lady_test, lady_test does not include lady1. Please provide the escape method. 3 ksanswer: select... from... where... like '/_ %' escape '/'; 3, Insert into T (COL) values (CHR (ASCII ('&'); (method 1) For example, insert a special character '&' SQL> show define Define "&" (hex 26 )? <--- The default value of define is '&' SQL> set define off SQL> show define Define off SQL> insert into <table_name> values ('at & T '); / 1 row created (method 2) SQL> show escape Escape off <--- the default value of escape is 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 .; 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 ('_ '); 1 row inserted SQL> select B from a where instr (B, '_ a')> 0; B ---------- _ 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 '\' This article is from 51cto. com technical blog