Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2010
    Posts
    18

    Question Unanswered: Help finding Mixed case records

    Hello,

    I am newbie in MySql.

    I require a query which can find mixed case records in a table

    eg.
    TABLE1
    Abc Xyz (title case)
    ABC XYZ (upper case)
    abc xyz (lower case)
    AbC Xyz (mixed case)
    Abc XyZ (mixed case)

    Output:

    AbC Xyz (mixed case)
    Abc XyZ (mixed case)

    Any Help ?

    Thanks

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by andy982183 View Post
    Any Help ?

    Usually MySQL is set up to ignore case. I believe what you need to use are regular expressions. I think something like this will do it for you:
    Code:
    select name from YourTable where name regexp '[A-Z][a-z]*[A-Z]'

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using the MySQL string functions UPPER and LOWER
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by mike_bike_kite View Post

    Usually MySQL is set up to ignore case. I believe what you need to use are regular expressions. I think something like this will do it for you:
    Code:
    select name from YourTable where name regexp '[A-Z][a-z]*[A-Z]'
    Hello mike,

    Thanks for ur reply.

    I tried ur query, but it displays all records except null values

    any different solution for this problem ?

    Thanks

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You may need to use the binary keyword (I'd experiment a bit):
    Code:
    select name from YourTable where name regexp BINARY '[A-Z][a-z]*[A-Z]'

  6. #6
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by mike_bike_kite View Post
    You may need to use the binary keyword (I'd experiment a bit):
    Code:
    select name from YourTable where name regexp BINARY '[A-Z][a-z]*[A-Z]'
    Hey Mike,

    I tried this query.
    It displays UPPER as well as Mixed case records.
    It's works for me.

    Thanks a lot for ur help

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Sorry, thought you wanted those as well (3rd time lucky):
    Code:
    select name from YourTable where name regexp BINARY '[A-Z][a-z]+[A-Z]'

  8. #8
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by mike_bike_kite View Post
    Sorry, thought you wanted those as well (3rd time lucky):
    Code:
    select name from YourTable where name regexp BINARY '[A-Z][a-z]+[A-Z]'
    hey thanks a lot mike.
    u made my day !

  9. #9
    Join Date
    Mar 2006
    Posts
    56
    Quote Originally Posted by healdem View Post
    consider using the MySQL string functions UPPER and LOWER
    Like this?

    Code:
    SELECT name
      FROM table
     WHERE NOT name IN (UPPER(name), LOWER(name))

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    or for efficiency and speed, store the a column of this data in either upper or lower case and query it accordingly, this way you use the index.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •