HOW TO ESCAPE SPECIAL CHARACTER IN SQL QUERY IN Oracle9i Enterprise Edition Release 9.2.0.6.0

Select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_N CLERK 7902 17-Dec-1980 1064.8 20
7499 ALLEN_
SALESMAN 7698 20-Feb-1981 1600 300 30

select * from emp where ename like '%*_%' escape '*' --Here we are telling to escape the character(s) immetiadely after the asterisk.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_N CLERK 7902 17-Dec-1980 1064.8 20


select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_%N CLERK 7902 17-Dec-1980 1064.8 20
7499 ALLEN% SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 3959.73 20
7654 MARTIN SALESMAN 7698 28-Sep-1981 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-1981 2850 30
7782 CLARK MANAGER 7839 9-Jun-1981 3260.95 10
7788 SCOTT ANALYST 7566 9-Dec-1982 3993 20
7839 KING PRESIDENT 17-Nov-1981 6655 10
7844 TURNER SALESMAN 7698 8-Sep-1981 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-1983 1464.1 20
7900 JAMES CLERK 7698 3-Dec-1981 950 30
7902 FORD ANALYST 7566 3-Dec-1981 3993 20
7934 MILLER CLERK 7782 23-Jan-1982 1730.3 10


select * from emp where ename like '%*_*%%' escape '*' --Here we are telling to escape the character(s) immetiadely after the asterisk
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_%N CLERK 7902 17-Dec-1980 1064.8 20


select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH&N CLERK 7902 17-Dec-1980 1064.8 20
7499 ALLEN% SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 3959.73 20
7654 MARTIN SALESMAN 7698 28-Sep-1981 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-1981 2850 30
7782 CLARK MANAGER 7839 9-Jun-1981 3260.95 10
7788 SCOTT ANALYST 7566 9-Dec-1982 3993 20
7839 KING PRESIDENT 17-Nov-1981 6655 10
7844 TURNER SALESMAN 7698 8-Sep-1981 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-1983 1464.1 20
7900 JAMES CLERK 7698 3-Dec-1981 950 30
7902 FORD ANALYST 7566 3-Dec-1981 3993 20
7934 MILLER CLERK 7782 23-Jan-1982 1730.3 10


select * from emp where ename like '%*&%' escape '*'
13:19:36 ORA-01424: missing or illegal character following the escape character --As '&' does not have any special meaning in pattern-search in SQL query.

---: My blog is not responsible for any damages happened from the suggestion of my blog :---
Reach me at : m_a_hasim@yahoo.com