REGEXP_SUBSTR return all matches (mariaDB)

Question

I need to get all the matches of a regular expression in a text field in a MariaDB table. As far as I know REGEXP_SUBSTR is the way to go to get the value of the match of a regular expression in a text field, but it always returns after the first match and I would like to get all matches.

Is there any way to do this in MariaDB?

An example of the content of the text field would be:

@Generation {
// 1
True =>
    `CP?:24658` <= `CPV?:24658=57186`;
//`CP?23432:24658` <= `CPV?:24658=57186`

// 2
`CP?:24658` <> `CPV?:24658=57178` =>
    `CP?:24656` <> `CPV?:24656=57169`;

And the select expression that I'm using right now is:

select REGEXP_SUBSTR(textfield,'CP\\?(?:\\d*:)*24658') as my_match from table where id = 1243;

Which at the moment returns just the first match:

• CP?:24658

And I would like it to return all matches:

• CP?:24658
• CP?23432:24658
• CP?:24658

Graham

There would need to a function which can return something like an array of results, and I don't know if MariaDB has anything like that. But, do you have to do this work inside the database in the first place? – Melburn

Yup it is the fastest way since I have to get all the matches of potentially thousands of records of the database to process them. – Graham

Answer 1

1. Use just REGEXP to find the interesting rows. Put those into a temp table
2. Repeatedly process the temp table -- but remove the SUBSTR as you use it.

What will you be doing with each substr? Maybe that will help us devise a better approach.

Heterogeneous