CoderZone.org

Category: >> Oracle PL/SQL >> SELECT: Case insensitive search Bookmark and Share

<< lastnext >>

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 Wi...
» SELECT: Partition Select
» SELECT: Select For Update
» SELECT: Using Functions
» SELECT: Get DISTINCT / UNIQUE valu...
» SELECT: Get UNIQUE / DISTINCT valu...
» 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 state...
» Inserting into SELECT statement
» INSERT with Select

Comment: (none)

Author: CoderZone
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'
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 28