1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

wild card character

Discussion in 'Oracle' started by srijith.ramachandran, Mar 17, 2009.

  1. 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
     
  2. xpi0t0s

    xpi0t0s 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
     

Share This Page