wild card character

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

  1. srijith.ramachandran

    srijith.ramachandran New Member

    Joined:
    Mar 16, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Aug 6, 2004
    Messages:
    3,009
    Likes Received:
    203
    Trophy Points:
    63
    Occupation:
    Senior Support Engineer
    Location:
    England
    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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice