How to Perform Case-Insensitive Searches in Oracle?

Perform case-insensitive search in Oracle database

Comparison operators (such as LIKE, =, etc.) in Oracle database are case-sensitive by default. This can present a challenge when you need to search data without regard to case. To solve this problem, Oracle provides several ways to perform case-insensitive searches without relying on full-text indexes.

Method 1: Case conversion

You can use the UPPER() or LOWER() function to force all data to be in the same case. For example:

<code class="language-sql">select * from my_table where upper(column_1) = upper('my_string');</code>

or

<code class="language-sql">select * from my_table where lower(column_1) = lower('my_string');</code>

This may require a full table scan if column_1 is not indexed on upper(column_1) or lower(column_1) respectively. To avoid this, create a function-based index:

<code class="language-sql">create index my_index on my_table ( lower(column_1) );</code>

For the LIKE operator, add % around the search string:

<code class="language-sql">select * from my_table where lower(column_1) LIKE lower('my_string') || '%';</code>

Method 2: Regular expression

The REGEXP_LIKE() function introduced starting with Oracle 10g provides case-insensitive search by specifying the 'i' match parameter:

<code class="language-sql">select * from my_table where regexp_like(column_1, '^my_string$', 'i');</code>

To use this as an equality operator, specify the beginning and end of the string (using ^ and $):

<code class="language-sql">select * from my_table where regexp_like(column_1, '^my_string$', 'i');</code>

For LIKE equivalent search, remove ^ and $. Note that the search string may contain characters that are interpreted differently by the regular expression engine.

Method 3: Session-level configuration

The NLS_SORT parameter defines the sort order of comparisons, including = and LIKE. You can enable case-insensitive sorting for all queries in a session by setting it to BINARY_CI:

<code class="language-sql">alter session set nls_sort=BINARY_CI;</code>

You may also want to set NLS_COMP to LINGUISTIC to force language comparison:

<code class="language-sql">alter session set nls_comp=LINGUISTIC;</code>

To improve performance, you can create a language index:

<code class="language-sql">create index my_linguistc_index on my_table (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));</code>

The above is the detailed content of How to Perform Case-Insensitive Searches in Oracle?. For more information, please follow other related articles on the PHP Chinese website!