Bug #79428 No way to extract a substring matching a regex
Submitted: 26 Nov 2015 16:04
Reporter: teo teo Email Updates:
Status: Open Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[26 Nov 2015 16:04] teo teo
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:

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


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


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.