Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: SELECT: Case insensitive search Jump to:  
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 With D...
» SELECT: Partition Select
» SELECT: Select For Update
» SELECT: Using Functions
» SELECT: Get DISTINCT / UNIQUE values
» SELECT: Get UNIQUE / 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'


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 71 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?