Mysql regexp fails

Question

I'm trying to find any records in table customers where the name does not contain letters. The following is what I am using. When ran, it doesn't return any records found. Would someone point out my mistake, please?

    table customers {
    name = Ð•arnings on thÐµ IntÐµrnet from
    }

    SELECT name from customers WHERE name NOT REGEXP '[a-zA-Z]' ;

Bill Karwin · Accepted Answer · 2020-04-07T22:35:15.127

If I compare a string using REGEXP '[a-zA-Z]', it matches if any character in the string matches a letter in that regexp. The string you show does contain letters like a, r, n, i, g, s. Any one of those is enough to satisfy the REGEXP comparison.

mysql> select 'Ð•arnings on thÐµ IntÐµrnet from' REGEXP '[a-zA-Z]' as ok;
+----+
| ok |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

The negation NOT REGEXP is the same as NOT (expr REGEXP pattern). It simply reverses the result 1 for 0, or 0 for 1.

mysql> select 'Ð•arnings on thÐµ IntÐµrnet from' NOT REGEXP '[a-zA-Z]' as ok;
+----+
| ok |
+----+
|  0 |
+----+

You said you want to match names that do not contain letters. What I think you mean is that you want to match names that contain any characters that are not letters, which is a different test.

mysql> select 'Ð•arnings on thÐµ IntÐµrnet from' REGEXP '[^a-zA-Z]' as ok;
+----+
| ok |
+----+
|  1 |
+----+

The characters Ð • µ are not in the range [a-zA-Z] and the way to express the complementary range of characters is to use [^a-zA-Z]. That is, with the ^ character inside the square brackets, a character would match if it is NOT one of the characters in that range.

See also https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax under the item:

[a-dX], [^a-dX]

Re your comment:

I tested checking for the characters you mention, including space, apostrophe, dot, and dash:

mysql> select 'Mr. Tim O''Toole' regexp '[^a-zA-Z \'.-]' as ok;
+----+
| ok |
+----+
|  0 |
+----+

mysql> select 'Mr. Tim $ O''Toole' regexp '[^a-zA-Z \'.-]' as ok;
+----+
| ok |
+----+
|  1 |
+----+

You don't need to put a backslash before the -, but it does need to be handled specially:

To include a literal - character, it must be written first or last.

This is in the documentation I linked to.

Thank you very much for the explanation. I made the change you mentioned and it works. But what I really trying to do is to allow all names that do not have numbers. So a name could have a period, space, apostrophe and a dash, like Mr. Tim O;Toole. To that end, I tried this - [^a-zA-Z.\' ] - andit seems to work as I want. Although [^a-zA-Z.\'\- ] causes a mysql error. And if I just try yours - [^a-zA-Z], it says it is OK. Do I need to add those extra characters and, if so, why does the dash cause a failure? — user3052443, Apr 07 '20 at 22:16
In case anyone else runs across the hyphen problem, I found it works if it is placed first, like this: [^-a-zA-Z.,\' ] — user3052443, Apr 08 '20 at 18:10

Mysql regexp fails

1 Answers1