SELECT first_name, last_name
FROM employees
WHERE UPPER(last_name) = 'SMITH';
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.
0 Komentar