6

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
Driond
  • 113
  • 1
  • 4
  • 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? – Tim Biegeleisen Oct 04 '18 at 04:45
  • 1
    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. – Driond Oct 04 '18 at 06:18

1 Answers1

2
  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.

Rick James
  • 135,179
  • 13
  • 127
  • 222