wild card character

srijith.ramachandran's Avatar, Join Date: Mar 2009
Newbie Member
Hi All,

i have a query...

i know i have received a reply on this and that says "use like"..i know it works with like operator..but my question is why i can't use wild card character with "=" sign...can you please clear my doubt.....can you please give me a more detailed explanation on why it does'nt work;

why does the below sql query ie (a) returns no row, while the statement (b) displays rows

(a) select * from sri where ename='%AME%';

----->no rows selected

(b)
SQL> select * from sri where ename='JAMES';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
I thought we'd already answered this. Ah yes:
http://www.go4expert.com/showthread.php?t=16559

= tests for equality. JAMES is not equal to %AME% because J is not equal to % and S is not equal to %. The = operator does NOT handle wild characters.

If you want to handle wild characters then you have to use LIKE in place of =. So:

select * from sri where ename LIKE '%AME%';

might give the following results, assuming JAMES, DAMES and CAMEL all exist of course:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7901 DAMES CLERK 7698 03-DEC-81 950 30
7902 CAMEL CLERK 7698 03-DEC-81 950 30

But select * from sri where ename='%AME%';
will only return data if %AME% actually exists as a data item literally as percent AME percent, i.e.:

select * from sri where ename='%AME%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7903 %AME% CLERK 7698 03-DEC-81 950 30