Simulating regex capture groups in mysql

As far as I can tell MySQL does not support retrieving the value of a capture group from a regex match. I have found a server side extensions (lib_mysqludf_preg) which would add this functionality but I will not be able to install this extension in my environment.

So, I’m looking for a way to simulate capturing a part of a regex match as a column in an SQL query.

My data looks like the following (and I can’t change the data format on the server):

| Version                     |
|                     |
|                 |
| Obsidian-            |
| Obsidian-        |
| customObsidian- |
| custom11.21.31.41          |

I’m looking to capture each of the last 4 digits from each row. The digits are always that last part of the value and they are always separated by dots. The following regex would match all of the values that I want:


The result I’m hoping for is some combination of functions to capture each digit as a column so that I can use the digit in the where clause of my query as well as being able to get the version number back.

    function1(...) as version1,
    function2(...) as version2,
    function3(...) as version3,
    function4(...) as version4
FROM Version
WHERE version1 > 5;


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

After some trial and error I came up with the following query that does what I need. Basically I seperate numbers off the end of the string and then remove that many characters before separating the next number. The version1 column is limited to positive 2 digit numbers, but that that’s a limitation that I can live with in my case.

    IF(CAST(RIGHT(SUBSTRING_INDEX(LEFT(version,CHAR_LENGTH(version) - CHAR_LENGTH(SUBSTRING_INDEX(version, '.', -3)) - 1), '.', -1),2) AS DECIMAL) > 0, 
        CAST(RIGHT(SUBSTRING_INDEX(LEFT(version,CHAR_LENGTH(version) - CHAR_LENGTH(SUBSTRING_INDEX(version, '.', -3)) - 1), '.', -1),2) AS DECIMAL), 
        CAST(RIGHT(SUBSTRING_INDEX(LEFT(version,CHAR_LENGTH(version) - CHAR_LENGTH(SUBSTRING_INDEX(version, '.', -3)) - 1), '.', -1),1) AS DECIMAL)) AS version1,
    SUBSTRING_INDEX(LEFT(version,CHAR_LENGTH(version) - CHAR_LENGTH(SUBSTRING_INDEX(version, '.', -2)) - 1), '.', -1) as version2,
    SUBSTRING_INDEX(LEFT(version,CHAR_LENGTH(version) - CHAR_LENGTH(SUBSTRING_INDEX(version, '.', -1)) - 1), '.', -1) as version3,
    SUBSTRING_INDEX(version, '.', -1) as version4
FROM Version
HAVING version1 >= 5

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x