Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The default is case sensitive, so the following query returns no lowercase. In this case, the returned result will contain missing data.
1 | select * from person where name like 'A%' ; |
If the result of the query must return both upper and lower case records, the following changes must be made.
NLS_COMP = LINGUISTIC and NLS_SORT = BINARY_AI
If it is desired to be valid for all users, it can be changed as a parameter.
If you only want to apply to specific users, you can do this with the database logon trigger.
Change parameter for all users:
1 2 | SQL> alter system set NLS_COMP=LINGUISTIC scope=spfile sid='*'; SQL> alter system set NLS_SORT=BINARY_AI scope=spfile sid='*'; |
The database must then be restarted.
Database Logon Trigger for spesific user:
If you want it to be valid only for a user , you can do this with logon trigger as follows at the session level , which can only be valid during the session when the user logs in.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TRIGGER SYS.CASE_INSENSITIVE_TRIGGER AFTER LOGON ON DATABASE BEGIN if (sys_context('USERENV', 'SESSION_USER') in ('USER1','USER2')) then EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_AI'; end if; END case_insensitive_trigger; / |
