Ext.: http://blog.chinaunix.net/uid-26896647-id-3433968.html
Question Description: If there are special characters such as ' & ', ' _ ', '% ' on a field in a table, and we have to use these special characters in the Where condition?
1. Create a table containing these special characters
Sql> CREATE TABLE T_test_escape (name VARCHAR2 (20)); Table created sql> insert into T_test_escape (name) VALUES (' &_hello '); 1 row inserted
--This will prompt me to enter the value of the variable _hello, I did not enter any value, so it is empty! Sql> select * from T_test_escape; NAME--------------------
The result naturally also is empty sql> truncate TABLE t_test_escape; Table truncated
--use set define off to turn off the function of the substitution variable sql> set define offsql> INSERT into T_test_escape (name) VALUES (' &_hello '); 1 row inserted sql> insert into T_test_escape (name) VALUES ('%%_hello '); 1 row inserted sql> insert into T_test_escape (name) VALUES (' Oracle%&_hello '); 1 row inserted sql> commit; Commit Complete sql> select * from T_test_escape; NAME--------------------&_hello%%_hellooracle%&_hello
--After using set define off to turn off the variable substitution function, it is possible to insert special characters containing &.
2. Use the Escape keyword to view the character's string in a fuzzy query that contains a% sql> select * from T_test_escape where name like '%a%% ' escape ' a '; NAME--------------------%%_hellooracle%&_hello
--The escape character used above is ' a '
3. Use the Escape keyword to query the string containing ' & ' because this is still set define off so this time & is not a special character, so the following query will be error sql> select * from T_test_ Escape where name like '%a&% ' escape ' a '; SELECT * from T_test_escape where name like '%a&% ' escape ' a ' ora-01424:missing or illegal character following the ES Cape character sql> insert into T_test_escape (name) VALUES (' Oracle%&hello '); 1 row inserted sql> commit; Commit Complete sql> select * from T_test_escape where name like '%a&h% ' escape ' a '; SELECT * from T_test_escape where name like '%a&h% ' escape ' a ' ora-01424:missing or illegal character following the E Scape character
After set define off the substitution variable function can be used to directly & as ordinary characters, without escapesql> select * from the t_test_escape where name like '%&h% '; NAME--------------------Oracle%&hello Use set define on to open the alternate variable function sql> set define on; Sql> SELECT * from T_test_escape where name like '%&h% ';
--This will prompt me to enter the value of the variable h, because I have not entered any value, this SQL condition is equivalent to like '% ', so return all data NAME--------------------&_hello%%_hellooracle% &_hellooracle%&hello--Use the Escape keyword to specify a specific escape character try Sql> select * from T_test_escape where name is like '%a&h% ' Escape ' a ';
NAME--------------------
Or will I be prompted to enter the value of the variable h
--The following ASCII to bypass this barrier by querying the ' & ' sql> select ASCII (' & ') from dual; ASCII (' & ')----------38
--Use CHR (38) to replace special characters ' & ' sql> select * from T_test_escape where name like '% ' | | Chr (38) | | ' h% '; NAME--------------------Oracle%&hello
4. Use the Escape keyword fuzzy query to include ' & ' string sql> select * from T_test_escape where name like '%a_% ' escape ' a '; NAME--------------------&_hello%%_hellooracle%&_hello
5. Below I will replace the special character of the variable to $ try and see if I can use the fuzzy match to match the special character ' & ' sql> set define $SQL > select * from t_test_escape where name like '%& amp;h% '; NAME--------------------Oracle%&hello
Summary: For use escape keyword to escape special characters, not for all special characters can escape success, the above experiment shows that for '% ', ' _ ', ' & ', use Escape is able to escape '% ', ' _ ', but can not escape ' & ', of course, this is only the default, if we set the identifier of the binding variable to a non-default $, then we can treat ' & ' as a normal character. If you do not change the default identifier of the bound variable, then use CHR (38) instead of ' & '!
From for notes (Wiz)
Use the Escape keyword in Oracle to implement like Match Special characters, and escape of & characters
