"Saiful" <md-saifulamri_omar@hp.com> wrote:
>
>Hi
>
>Oracle is case sensitive. If I have a field called Status that has value
>of varchar that says, "Wait" and if my select statement goes like this;
>"SELECT * FROM TABLENAME WHERE Status = 'WAIT'", most likely than not that
>I would not receive any records return to me with Status = "Wait".
>
>Is there a way in Oracle where you can turn off this character sensitivity.
>
>rgds,
>Saiful

Myles M. Waito

Oracle ( My Course of study is Oracle 8i,but, this is available in 7 and
probably lower) is to use the either of the scalar functions LOWER() or UPPER()
or a combination.

Syntax

LOWER
Description : Conversts all uppercase letters of the value of the parameter
to lower case. UPPER( ) is the mirror scalar performing the reverse operation.

Data Type: alphaneumeric

Examples :

1. LOWER('Myles')
-> 'myles'
2. Use in a select statement to render all cases to lower case.
SELECT * FROM TABLENAME WHERE Status = LOWER('wait')
STATUS
------
wait


3. In your case where you have an initial capital and lower case you will
have to employ two scalars. The first scalar is INITCAP and the second is
LOWER.

INITCAP
Description : Returns the first letter of each word in the value of an alphaneumeric
expression in upper case. The first letter of a word i the first letter
of the value or the first letter following a blank.

Data type : alphanumeric

1.INITCAP('Database')
-> 'Database'
2.INITCAP('d a t a')
->'D A T A'

Coupling the two Scalars into your query you can find all the particular
records where this is true :

SELECT * FROM TABLENAME WHERE Status = INITCAP(LOWER('WAIT'))

STATUS
------
Wait


Comment : If you have this situation where you have three types of cases
you may want to do two things the update function and convert every thing
to a common case.


Hope this helps.

Regards
Myles M. Waito
mwaito@sympatico.ca