Bug #79428 No way to extract a substring matching a regex
Submitted: 26 Nov 2015 16:04 Modified: 8 Mar 15:45
Reporter: teo teo Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[26 Nov 2015 16:04] teo teo
Description:
There's no way at all to extract/get the part of a string that matches a regular expression.

That is, there's no way to accomplish:

  SELECT PART_OF_STRING_MATCHING_REGEX(mytable.myfield, 'myRegEx') FROM mytable

This is a very basic and vital functionality and there is no possible way to achieve it.

How to repeat:
Try to accomplish the above

Suggested fix:
Either

1) add a function that could be called e.g. REGEXP_EXTRACT(string, regex), which would return the part of string matching regex

or 

2) add a function e.g. REGEXP_LOCATE(string, regex), which would work like LOCATE() except that it would take a regex rather than a regular string as the second parameter. This, combined with SUBSTR().... wait a moment, no, you would need the length of the matched substring

or

3) add a function e.g. REGEXP_REPLACE() which would work like REPLACE, but it would accept a regex rather than a substring. This would provide a convoluted workaround for the issue at hand (and also the possibility to replace a substring based on a regex)

Note that this matches the definition of "critical" in that "a significant functionality is missing" (more than significant, essential).
It's astonishing that such functionality is not there.
[7 Feb 14:28] Sinisa Milivojevic
Hi,

Thank you very much for your bug report.

In order to verify the bug we need a fully repeatable test case. Hence, please provide us with dumps of all tables involved, including the stored function(s) that are not able to extract a substring matching a regular expression.

Thanks in advance.
[11 Feb 13:04] Sinisa Milivojevic
HI,

We have  read your report and we need a way to repeat the behaviour, so that we can verify the bug.
[11 Feb 13:13] Sinisa Milivojevic
Hi,

Are you actually asking for new functionality that does not exist now ???

In that case, no test case is needed. All that we need is that you confirm that what you are after is a new feature request. A feature that would return a string matching regular expression search.

In that case, have you looked at the functions:

REGEXP_INSTR()

and

REGEXP_SUBSTR()

especially the last one !!!!!
[8 Mar 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Mar 13:51] Sinisa Milivojevic
Please, mind a language.

This is a public forum.