Case insensitive searching in Oracle

In Oracle, you can perform case-insensitive searching using the UPPER or LOWER function to convert the search string and the column to the same case before comparing them. For example, the following query retrieves all employees whose last name is 'SMITH' regardless of the case of the letters:

SELECT first_name, last_name
FROM employees
WHERE UPPER(last_name) = 'SMITH';

Alternatively, you can also use the LOWER function

SELECT first_name, last_name

FROM employees

WHERE LOWER(last_name) = 'smith';

You can also use the "INITCAP" function, which will capitalize the first letter of each word and lowercase the rest of the letters.

SELECT first_name, last_name

FROM employees

WHERE INITCAP(last_name) = 'Smith';

It's worth noting that using the UPPER or LOWER function on the column will affect performance, as it will need to convert the column on every row. If you need to perform case-insensitive searching frequently, creating a new column in the table that contains the column data in a consistent case is recommended and using that column for searching instead.

Also, starting from Oracle 12c, you can use the "ILIKE" operator who is case-insensitive like the operator.

SELECT first_name, last_name

FROM employees

WHERE last_name ILIKE 'smith';

It's also worth noting that the case-insensitive search behaviour depends on the NLS_SORT parameter set for the database, so it's recommended to check the current value of this parameter before performing case-insensitive searches.