Search code examples
sqloraclewhere-clausesql-like

Why some of the results for SQL query containing certain alphabets are missing?

I have following table enter image description here

When I run following query, it should have shown all instructor name containing 's' but it doesnot. The query I've written:

SQL> SELECT instructor_name
  2  FROM instructor
  3  WHERE instructor_name LIKE '%s%';

The result is:

enter image description here

What is the problem here? Should not Balbir Silakar and Saurav Pangeni too must appear on the result?

Solution

  • 's' and 'S' are two different things if your column has a case-sensitive collation.

    Alas, Oracle does not provide a case-insensitive version of like (usually called ilike in other databases).

    You could do:

    where instructor_name like '%s%' or instructor_name like '%S%'

    Or:

    where lower(instructor_name) like '%s%'

    Or, you can use regexp_like(); it takes a third argument that can be used to make the search case insensitive.

    where regexp_like(instructor_name, 's', 'i')

    I would not be surprised that the regex would be the fastest option out of the three.