I have following table
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:
What is the problem here? Should not Balbir Silakar and Saurav Pangeni too must appear on the result?
'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.
As others have mentioned, s (chr(115)) and S (chr(83)) are two different things, and there is no s in 'Balbir Silakar' or 'Saurav Pangeni'.
From Oracle 12.2 you can use
where instructor_name collate binary_ci like '%s%';
You can additionally ignore accents with
where instructor_name collate binary_ai like '%s%';
Your database (by default I think) is case-sensitive, thus the LIKE matching mechanism is case-sensitive. Those names contain an upper-case S.
Here's a Q&A on how to make the search not case-sensitive. Perform a Case insensitive Like query in a case sensitive SQL Server database
For case insensitive check use regexp_like
where regexp_like(instructor_name , 's', 'i');