MySQL & Regex (checking for proper name capitalization)

Question

Hi Everyone, I'm trying to check if a name has invalid characters, so far I've managed to get everything I need apart from checking for capitalization, I've tried using

SELECT BINARY('jiLl') REGEXP('[[:upper:]]+');

but unfortunately that also flags properly formatted names, as in (Jack), is it possible to have the regex ignore the first character of the name, and if so how?

Thank you in advance, --a

Answer 1

Take one step back and rethink ;)

Give me all instances that don't start with a capital letter and the rest are lower-case:

mysql> SELECT BINARY('JacK') NOT REGEXP('^[[:upper:]][[:lower:]]+$') AS is_invalid;
+------------+
| is_invalid |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT BINARY('jiLl') NOT REGEXP('^[[:upper:]][[:lower:]]+$') AS is_invalid;
+------------+
| is_invalid |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT BINARY('Jack') NOT REGEXP('^[[:upper:]][[:lower:]]+$') AS is_invalid;
+------------+
| is_invalid |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)