Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   wild card character (http://www.go4expert.com/forums/wild-card-character-t16570/)

srijith.ramachandran 18Mar2009 00:08

wild card character
 
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 18Mar2009 03:08

Re: wild card character
 
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


All times are GMT +5.5. The time now is 17:18.