Re: Case insensitive search

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')

Regards,
Monte Carver Received on Wed Jan 31 2007 - 02:08:14 CST