How to query for formatted data in MySql

Question

How can I query for improperly formatted data in MYSQL?

For example, if I have the following data in my table:

1.John Doe
2.john doe
3.JOHN DOE
4.joHn DoE.

I'm looking for a query that would return rows 2, 3, and 4.

Answer 1

SELECT id,name FROM `Table1` 
WHERE `name` NOT REGEXP BINARY '([A-Z]{1,1}[a-z]+ [A-Z]{1,1}[a-z]+)$';

Output

id  name
2   john doe
3   JOHN DOE
4   joHn DoE.

Live Demo

http://sqlfiddle.com/#!9/3092b3/75