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

Answer 2

To ensure data integrity it is a better practice to store names/surnames/street names/addresses etc in separate fields and without any capitalization. For display purposes, you can apply capitalization. This way you will save some trouble figuring out if John = john = jOhn.

Run the following to normalize values:

 UPDATE Table1 SET name = LOWER(name)