Re: selecting case senstive data

On 14 Nov 2002 06:33:13 -0800, ahuch_at_tiscali.de (Axel Huch) wrote:

>Hi everybody,
>
>I'm desperatly looking for the right configuration value which allows
>to select data in a case sensitive way.
>I'm using Oracle 8.1.7 PE.
>I have to diffentiate between "diagram" and "Diagram" in a select
>statement.
>
>I search the documentation in the NLS section but it tells me nothing
>real about it.

The query is case-sensitive by default, i.e.

where colvalue = 'diagram'
where colvalue = 'Diagram'

are considered different. Do you mean you want them to be considered the same (case-insensitive)? If so, you'll need to use an UPPER() or LOWER() function

where upper(colvalue) = upper('Diagram')

and if you have EE, define a function-based index on upper(colvalue) so that the uppercase value is already computed and indexed. Function-based indexes also requires init parameters (QUERY_REWRITE_ENABLED and possibly QUERY_REWRITE_INTEGRITY) and a privilege (QUERY REWRITE).

John Received on Sat Nov 16 2002 - 13:39:18 CST