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                     |
+-----------------------------+
| 1.2.3.4                     |
| 10.20.30.40                 |
| Obsidian-1.2.3.4            |
| Obsidian-11.21.31.41        |
| custom\Obsidian-11.21.31.41 |
| custom\11.21.31.41          |
+-----------------------------+

开发者_Python百科

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:

.*[[:digit:]]+\\.[[:digit:]]+\\.[[:digit:]]+\\.[[:digit:]]+$

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.

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

---

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.

SELECT
    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
;