Snippet Name: SELECT: Case insensitive search
Description: Prior to Oracle10g release 2, case insensitive queries required special handling:
- You could transform data in the query to make it case insensitive
- You could create an index using upper(client_city) on client_city
- Use a trigger to transform the data to make it case insensitive (or store the data with the to_lower or to_upper BIF.
- Use Alter session commands
Fortunately, Oracle10g release 2 takes a new approach to case insensitive searches, as shown in the second example to the right.
Also see:
» TABLE: Using Select Statement With Data
» SELECT: Partition Select
» SELECT: Select For Update
» SELECT: Using Functions
» SELECT: Get DISTINCT or UNIQUE values
» SELECT: Get UNIQUE and DISTINCT values
» SELECT: Scalar Select
» SELECT with HAVING Clause
» SELECT with GROUP BY Clause
» SELECT with WHERE Clause
» SELECT with SAMPLE clause
» SELECT placement
» SELECT into a table
» SELECT name columns
» SELECT
» UPDATE: Update from a SELECT statement
» Inserting into SELECT statement
» INSERT with Select
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009
|
-- case insensitive searches using Oracle10g release 1 and lower:
ALTER session SET NLS_COMP=ANSI;
ALTER session SET NLS_SORT=GENERIC_BASELETTER;
SELECT * FROM customer WHERE client_city = 'San Antonio';
-- Oracle10g's new method for case insensitive searches:
NLS_SORT=binary_ci
NLS_COMP=ansi
CREATE INDEX
caseless_city_index
ON
customer
(
NLSSORT( client_city, 'NLS_SORT=BINARY_CI')
);
ALTER session SET nls_sort=binary_ci;
SELECT * FROM customer WHERE client_city = 'San Antonio' |
