Results 1 to 7 of 7

Thread: like expression

  1. #1
    Contributing Member
    Join Date
    Jul 2007
    Answers
    42

    like expression

    hi
    i want fillter A to K at ename in emp table
    how is possible


  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: like expression

    First create a table and insert letter into that table as follows

    create table test(let char(1))

    insert into test_let values('A')
    insert into test_let values('B')
    insert into test_let values('C')
    insert into test_let values('D')
    insert into test_let values('E')
    insert into test_let values('F')

    then write this simple query to get the result

    select a.ename,a.job,a.sal
    from emp a, test b
    where ename like b.let||'%'

    Last edited by krishnaindia2007; 05-14-2008 at 11:23 PM.

  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: like expression

    The following query works only if you are using oracle 9i or above version.

    With test as
    ( select 'a' let from dual
    union all
    select 'b' let from dual
    union all
    select 'c' let from dual
    union all
    select 'e' let from dual
    union all
    select 'f' let from dual )
    select a.ename,a.job,a.sal
    from emp a , test b
    where ename like b.let||'%';


  4. #4
    Contributing Member
    Join Date
    Jul 2007
    Answers
    42

    Re: like expression

    can i use this method

    select * from emp where ename between 'A' and 'M'

    or

    select * from emp where ename between 'A' and 'L'
    union all
    select * from emp where ename like 'L%'


    or

    select * from emp where ename > 'L'
    union all
    select * from emp where ename like 'L%'


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: like expression

    >>select * from emp where ename between 'A' and 'M'

    Add substr function to the above query as follows
    select * from emp
    where substr(ename,1,1) between 'A' and 'M'

    Generally between operator is used with numeric and date data types not with character data types.


  6. #6
    Junior Member
    Join Date
    May 2008
    Answers
    7

    Re: like expression

    Quote Originally Posted by priyasp_msit View Post
    hi
    i want fillter A to K at ename in emp table
    how is possible
    SELECT ename
    FROM emp
    WHERE SUBSTR(ename,1,1) IN('A','B','C',-----------'K');


  7. #7
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: like expression

    Quote Originally Posted by chandra.pc View Post
    SELECT ename
    FROM emp
    WHERE SUBSTR(ename,1,1) IN('A','B','C',-----------'K');
    This will work ,but might be bit slow for large tables because of string operation and IN comparision.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact