Help finding Mixed case records

andy982183 · #1 (**permalink**) 03-31-10, 04:09

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

mike_bike_kite · #2 (**permalink**) 03-31-10, 04:19

Quote:

Originally Posted by andy982183

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]'

healdem · #3 (**permalink**) 03-31-10, 04:21

consider using the MySQL string functions UPPER and LOWER

andy982183 · #4 (**permalink**) 03-31-10, 04:40

Quote:

Originally Posted by mike_bike_kite

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

mike_bike_kite · #5 (**permalink**) 03-31-10, 04:57

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]'

andy982183 · #6 (**permalink**) 03-31-10, 05:27

Quote:

Originally Posted by mike_bike_kite

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

mike_bike_kite · #7 (**permalink**) 03-31-10, 07:19

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]'

andy982183 · #8 (**permalink**) 03-31-10, 07:39

Quote:

Originally Posted by mike_bike_kite

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 !

Ikviens · #9 (**permalink**) 03-31-10, 08:00

Quote:

Originally Posted by healdem

consider using the MySQL string functions UPPER and LOWER

Like this?

Code:

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

dav1mo · #10 (**permalink**) 03-31-10, 08:08

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

Internet Services

Web Development

Digital Marketing

Consumer Tech