How to return the REGEXP matches in MySQL

Question

MySQL provides REGEXP a.k.a. RLIKE for matching regular expressions.

How do I list the positions of the matches?

For instance, for a table with contents

X       | Y
--------+---------------
abcd    | abaab
efgh    | ababbaaabaaaab

I would like to be able to write something that takes Y and aa*b as arguments and returns all matches of the regexp aa*b in column Y, like this:

X       | POS   | LEN
--------+-------+-------
abcd    | 1     | 2
abcd    | 2     | 2
efgh    | 1     | 2
efgh    | 3     | 2
efgh    | 6     | 4
efgh    | 10    | 5

How can this be done?

Thus far, the best I can think of is a dirty workaround: try matching every possible substring, like is done here, and based on that, generate multiple results, either by using a stored procedure, or by generating and executing INSERT statements.

Is there a saner way to do this?

Answer 1

MySQL 8.0 has REGEXP_INSTR() for this.

Earlier versions of MySQL don't have that function.

The most common workaround is to return the query result including the full string that matches your regular expression — without knowing the position where it matches — and use some more feature-rich text matching functions in your favorite application language to find the position.