Can somebody help me understand this regex pattern?

Question

I have some knowledge of MySQL regexp syntax but I am not proficient. I was looking for a way to construct a pattern that selects all names from a mysql table that contain strange characters due to international input such as spanish names that have the symbol on top of the n.

I came upon this following pattern which I tried and it worked.

[^a-zA-Z0-9@:. \'\-`,\&]

The query is:

SELECT * 
FROM orders_table
WHERE customers_name REGEXP  '[^a-zA-Z0-9@:. \'\-`,\&]'

However I would like to understand how this pattern was constructed and what each part means.

This is called a **negated character class**: every character of the class must **not** be present. — Jan, Jul 23 '17 at 16:58

score 1 · Accepted Answer · answered Jul 23 '17 at 17:05

The idea is that anything between [...] is a character class, which matches any single character that's in the set between the [ and ].

Adding the ^ to the start of the list of characters means (as noted above) it's negated, which means it matches any character NOT in the set. Putting a ^ anywhere but the start of the [ ... ] means it's just a regular ^ character to match, and in no case does ^ inside a character class mean a start-of-line anchor.

Ranges work, such as a-z, and if you want a literal dash in the set, you either put it first (possibly after the ^), or quote it with \

Edit: the other special characters - @ : etc. - are not special in this context, they just match as regular characters.

To answer the SQL question, it looks like it's searching for any customer names that contains things **other than** what's in the list, so if a customer name had (say) a dollar sign, it would match. — Steve Friedl, Jul 23 '17 at 17:09

score 0 · Answer 2 · answered Jul 23 '17 at 17:07

[a-zA-Z0-9@:. \'-`,\&] - Let us consider all the important parts of this pattern.

[] - denotes a character class which matches any single character within [].

[a-zA-Z0-9] -- One character (lowercase or uppercase) that is in the range of a-z,A-Z OR 0-9.

All other characters which are present within the [] match for a particular single character. single quotes "'" and ampersand & are escaped by "\" because in sql, they are used for specific purposes.

To match a pattern which has a character other than any of these symbols within [], we use a negation "^" at the beginning.

Thus, as you have told ,your pattern selects all names from a mysql table that contain other strange characters.

Can somebody help me understand this regex pattern?

2 Answers2