Re: Unset the Case Sensitivity of Oracle - LOWER( )

[Myles Waito]

"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

[Amer Mallah]

I think you have it backwards -- your statement:
SELECT * FROM TABLENAME WHERE Status = INITCAP(LOWER('WAIT'))

would be the same as:
SELECT * FROM TABLENAME WHERE Status = 'Wait'

Which is the original problem. I think you meant to say:
SELECT * FROM TABLENAME WHERE INITCAP(LOWER(Status)) = 'Wait'


"Myles Waito" <mwaito@sympatico.ca> wrote:
>
>"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
>