Uses of Escape Special Characters in Oracle SQL queries

Uses of Escape Special Characters in Oracle SQL queries

SELECT employee_id, last_name, job_id
FROM   employees WHERE  job_id LIKE '%SA\_%' ESCAPE '\';

or

SELECT employee_id, last_name, job_id
FROM   employees WHERE  job_id LIKE '%SA_%';

Note: Above both quires given same output in HR schema. The ESCAPE clause identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

Following example we want to find all Oracle parameter that relate to I/O

LIKE “%_io_%’. Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:

select ksppinm 
from sys.x$ksppi 
where ksppinm like '%_io_%';

Here we can see that we did not get the answer we expected. The SQL displayed all values that contained "io", and not just those with an underscore. To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally:

select ksppinm 
from sys.x$ksppi 
where ksppinm like '%\_io\_%' ESCAPE '\';

###Here we create a table and test the scenario###

CREATE TABLE test (
test_col VARCHAR2(25));

INSERT INTO test VALUES ('23%45');
INSERT INTO test VALUES ('2345');
INSERT INTO test VALUES ('2365');
INSERT INTO test VALUES ('Dhaka Metro');
INSERT INTO test VALUES ('Rangpur');
COMMIT; 

--Now try to user to find out the value contain "%" special character--

SELECT *
FROM test
WHERE test_col LIKE '23_5';

SELECT *
FROM test
WHERE test_col LIKE '2%5';

SELECT *
FROM test
WHERE test_col LIKE '_3%5';

SELECT *
FROM test
WHERE test_col LIKE '%a%a %';

--Finally we can get the output using following query--

SELECT *
FROM test
WHERE test_col LIKE '%\%%' ESCAPE '\';


Above example is applicable for all version of oracle database in any platform

Cheers----