[sql] Case insensitive searching in Oracle

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

SQL> SET HEADING OFF
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY

NLS_COMP
BINARY


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         0

SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY_CI

NLS_COMP
LINGUISTIC


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         1

You can also create case insensitive indexes:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

This information was taken from Oracle case insensitive searches. The article mentions REGEXP_LIKE but it seems to work with good old = as well.


In versions older than 10gR2 it can't really be done and the usual approach, if you don't need accent-insensitive search, is to just UPPER() both the column and the search expression.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to case-sensitive

Are PostgreSQL column names case-sensitive? How can I convert uppercase letters to lowercase in Notepad++ How do I commit case-sensitive only filename changes in Git? In VBA get rid of the case sensitivity when comparing words? Changing capitalization of filenames in Git How to compare character ignoring case in primitive types Contains case insensitive Should URL be case sensitive? MySQL case sensitive query Are table names in MySQL case sensitive?

Examples related to case-insensitive

Are PostgreSQL column names case-sensitive? How to make String.Contains case insensitive? SQL- Ignore case while searching for a string String contains - ignore case How to compare character ignoring case in primitive types How to perform case-insensitive sorting in JavaScript? Contains case insensitive Case insensitive string as HashMap key Case insensitive searching in Oracle How to replace case-insensitive literal substrings in Java

Examples related to sql-like

SQL Server: use CASE with LIKE Create hive table using "as select" or "like" and also specify delimiter How do I find ' % ' with the LIKE operator in SQL Server? Using LIKE operator with stored procedure parameters SQL- Ignore case while searching for a string Is the LIKE operator case-sensitive with MSSQL Server? Using Eloquent ORM in Laravel to perform search of database using LIKE SQL 'LIKE' query using '%' where the search criteria contains '%' How to use "like" and "not like" in SQL MSAccess for the same field? MySQL SELECT LIKE or REGEXP to match multiple words in one record