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
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL