Re: Unset the Case Sensitivity of Oracle - LOWER( )
"Saiful" <firstname.lastname@example.org> wrote:
>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.
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.
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
2. Use in a select statement to render all cases to lower case.
SELECT * FROM TABLENAME WHERE Status = LOWER('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
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
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'))
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.
Myles M. Waito
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center