Is there a way to extract sub-matrices from regex in MySQL?

Question

I know about the existence of operators RLIKEand REGEX, but it seems that they cannot be used for this.

Is there a function or operator that will help me achieve separation of the text field and select it as two or more separate fields:

SELECT $1 as `field_a`, $2 as `field_b` FROM `table` WHERE `field` RLIKE '^(.+):(.+)$';

I am writing a log analyzer, so it would be very convenient to do this in SQL without additional crunching.

Answer 1

So, you just want to split the line into the first occurrence of ":"?

There are several ways to achieve this goal in MySQL.

Using your example, here are two approaches from my head. I hope they are useful to you:

select substr(`field`,1,instr(`field`,':')-1) as `field_a`,
  substr(`field`,instr(`field`,':')+1) as `field_b` 
FROM `table` 
WHERE `field` RLIKE '^(.+):(.+)$';

select left(`field`,instr(`field`,':')-1) as `field_a`,
  right(`field`,length(`field`)-instr(`field`,':')) as `field_b` 
FROM `table` 
WHERE `field` RLIKE '^(.+):(.+)$';