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 perform case insensitive search

Discussion in 'SQL PL/SQL' started by mukulverma2408, Oct 7, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    I have a requirement to write a query to find first name of all employees whose last name starts with any character from A to F, this is what i've tried but it dosen't seems to be the standard way
    Code (Text):

    select first_name from emp
     where lower(last_name) like 'A%'
     or lower(last_name) like 'B%'  
     or lower(last_name) like 'C%'  
     or lower(last_name) like 'D%'  
     or lower(last_name) like 'E%'  
     or lower(last_name) like 'F%' ;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And what IS the 'standard way'? As far as I know there isn't really a single way to do that, but different versions of the database provide additional options to return the desired results. Also if you're using the lower() function you cannot have capital letters in your comparison string because you'll return no results. For your query to return results you need to use the upper() function:

    Code (SQL):
    SQL>
    SQL> --
    SQL> -- Wrong way
    SQL> --
    SQL> -- Cannot compare lower case to upper case
    SQL> -- and return results
    SQL> --
    SQL>
    SQL> SELECT first_name FROM emp
      2    WHERE LOWER(last_name) LIKE 'A%'
      3    OR LOWER(last_name) LIKE 'B%'
      4    OR LOWER(last_name) LIKE 'C%'
      5    OR LOWER(last_name) LIKE 'D%'
      6    OR LOWER(last_name) LIKE 'E%'
      7    OR LOWER(last_name) LIKE 'F%' ;

    no ROWS selected

    SQL>
    SQL> --
    SQL> -- Modified queries returning results
    SQL> --
    SQL> SELECT first_name FROM emp
      2    WHERE UPPER(last_name) LIKE 'A%'
      3    OR UPPER(last_name) LIKE 'B%'
      4    OR UPPER(last_name) LIKE 'C%'
      5    OR UPPER(last_name) LIKE 'D%'
      6    OR UPPER(last_name) LIKE 'E%'
      7    OR UPPER(last_name) LIKE 'F%' ;

    FIRST_NAME
    --------------------
    Lex
    Bruce
    David
    Daniel
    John
    Shelli
    Karen
    Adam
    Laura
    Mozhe
    Curtis
    Alberto
    Gerald
    David
    Nanette
    Louise
    Sundar
    Amit
    Harrison
    Tayler
    Elizabeth
    Ellen
    Jean
    Alexis
    Julia
    Anthony
    Kelly
    Jennifer
    Sarah
    Britney
    Kevin
    Pat
    Hermann

    33 ROWS selected.

    SQL>
    SQL> SELECT first_name FROM emp
      2    WHERE LOWER(last_name) LIKE 'a%'
      3    OR LOWER(last_name) LIKE 'b%'
      4    OR LOWER(last_name) LIKE 'c%'
      5    OR LOWER(last_name) LIKE 'd%'
      6    OR LOWER(last_name) LIKE 'e%'
      7    OR LOWER(last_name) LIKE 'f%' ;

    FIRST_NAME
    --------------------
    Lex
    Bruce
    David
    Daniel
    John
    Shelli
    Karen
    Adam
    Laura
    Mozhe
    Curtis
    Alberto
    Gerald
    David
    Nanette
    Louise
    Sundar
    Amit
    Harrison
    Tayler
    Elizabeth
    Ellen
    Jean
    Alexis
    Julia
    Anthony
    Kelly
    Jennifer
    Sarah
    Britney
    Kevin
    Pat
    Hermann

    33 ROWS selected.

    SQL>
    SQL> --
    SQL> -- Regular expression solution
    SQL> --
    SQL> SELECT first_name
      2  FROM emp
      3  WHERE regexp_like(last_name, '^[(A,a,B,b,C,c,D,d,E,e,F,f)]');

    FIRST_NAME
    --------------------
    Lex
    Bruce
    David
    Daniel
    John
    Shelli
    Karen
    Adam
    Laura
    Mozhe
    Curtis
    Alberto
    Gerald
    David
    Nanette
    Louise
    Sundar
    Amit
    Harrison
    Tayler
    Elizabeth
    Ellen
    Jean
    Alexis
    Julia
    Anthony
    Kelly
    Jennifer
    Sarah
    Britney
    Kevin
    Pat
    Hermann

    33 ROWS selected.

    SQL>
    Notice that in later releases you can use the REGEXP_LIKE function to reduce the code and still return the correct results.
     
    mukulverma2408 likes this.