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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

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

  1. #1
    Myles Waito Guest

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


    "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


  2. #2
    Amer Mallah Guest

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


    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
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center