1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

query to retrieve names of the employee based on specified letters.

Discussion in 'SQL PL/SQL' started by sharanu, Apr 17, 2015.

  1. sharanu

    sharanu Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):

    //Hi SELF learner here, please do NOT assume that simply i am posting questions here, done/doing homework.
    //I wanted TO find the name OF the employees, whose names HAVING a, d AND s letters IN their names.TABLE name IS EMP AND COLUMN IS ENAME.
    1)sharan
    2) david
    3)smith
    4)efgh




     
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    SELECT ....
    FROM ....
    WHERE instr(name, 'a') > 0
       AND instr(name, 'd') > 0
       AND instr(name, 's') > 0
     
     
    sharanu likes this.
  3. sharanu

    sharanu Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    HI eras,
    Thanks for the solution, but it will be good only when a column have less number of names, if a column has more number of names then its difficult to use instr, apart from this solution is there any other command/function to find the solution.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It's not really clear what your objection to INSTR was. There are any number of ways to create such a filter. The regular expression functions in particular provide a very flexible method of searching. The following example uses the TRANSLATE function to eliminate all characters *except* the three you are searching for and then returns all non-NULL rows (i.e. rows where the name contains one or more of the three).

    Code (Text):
    SELECT first_name
    FROM   HR.employees
    WHERE  TRANSLATE(LOWER(first_name), 'adsbcefghijklmnopqrstuvwxyz', 'xxx') IS NOT NULL;
     
    sharanu likes this.