I'm trying to query for both the first and the second group of digits of a string on a MySQL table but after some attempts I only got the first group of digits to match and a copy of it as "the second group".

The query I used:

SELECT DISTINCT rif,REGEXP_SUBSTR(rif,"[0-9] ") AS num1, REGEXP_SUBSTR(rif, "[0-9] ") AS num2 FROM "table" WHERE rif LIKE " 5 QUEEN%"

And here is the output it returns:

Desired output:

How can I alter the query for it to return the desired output? Thanks in advance.

CodePudding user response：

The function needs additional parameters

REGEXP_SUBSTR(rif, "[0-9] ",1,2) as num2