Menu
As I had written in title, I have SQL query, run on Oracle DB, lets say:
SELECT * FROM TABLE WHERE TABLE.NAME Like 'IgNoReCaSe' If I would like, that the query would return either "IGNORECASE", "ignorecase" or combinations of them, how can this be done?
394
Starting with Oracle Database 10g Release 2 you can use the initialization parameters NLS_COMP and NLS_SORT to render all string comparisons case insensitive. We need to set the NLS_COMP parameter to LINGUISTIC, which will tell the database to use NLS_SORT for string comparisons.
Another way for case-insensitive matching is to use a different “collation”. The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default. The logic of this query is perfectly reasonable but the execution plan is not: DB2.
To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc' , 'ABC' , or 'aBc' .
The Oracle functions CASE, DECODE, and COALESCE all perform similar functionality. They can transform a value into another value.
Select * from table where upper(table.name) like upper('IgNoreCaSe'); Alternatively, substitute lower for upper.
76
Use ALTER SESSION statements to set comparison to case-insensitive:
alter session set NLS_COMP=LINGUISTIC; alter session set NLS_SORT=BINARY_CI; If you're still using version 10gR2, use the below statements. See this FAQ for details.
alter session set NLS_COMP=ANSI; alter session set NLS_SORT=BINARY_CI; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
