Re: Case insensitive search

On Wed, 31 Jan 2007 00:08:14 -0800, MRCarver wrote:

> Jumping on this thread... I have a question along the same lines.
>
> What is the most efficient way to perform
> A) A case insensitive search against one field in a search (e.g. a
> "USER_NAME" field) and
> B) A case sensitive search in another field within the same search
> (e.g. "PASSWORD" field).
>
> I have taken the following steps, but am not convinced this is the
> fastest and most efficient approach. Some advise on this subject is
> appreciated.
>
> 1) Make the session case insensitive by:
>
> alter session NLS_SORT='BINARY_CI'
> alter session NLS_COMP='LINGUISTIC'
>
> 2) Create an insensitive index on "USER_NAME" as follows to take
> advantage of the insensitive session with an insensitive index:
>
> CREATE UNIQUE INDEX "CASELESS_NAME_INDEX" ON "MY_TABLE"
> (NLSSORT("USER_NAME", 'nls_sort=''BINARY_CI'''))
>
> 3) Craft the query with the following where clause:
>
> where "PASSWORD" = REGEXP_SUBSTR("PASSWORD", pI_SUBMITTED_PWORD, 1,
> 1, 'i')
>

Even without making the session case insensitive, you can always create an index on lower(USER_NAME) and then search with a condition like lower(USER_NAME)='monte'

-- 
http://www.mladen-gogala.com