Is there a way to extract submatches from a regular expression in MySQL?

Question

I am aware of the existence of the RLIKE and REGEX operators, but it seems like they cannot be used for that.

Is there a function or an operator that would help me achieve splitting a text field and selecting 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 handy to do that in SQL without additional text-crunching.

Answer 1

So you just want to split the string on the first occurrence of ":"?

There are several ways to achieve this in MySQL.

Using your example, here are two approaches off the top of my head. Hopefully they are helpful 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 '^(.+):(.+)$';