Modeling regexp capture groups in mysql

Question

As far as I can tell, MySQL does not support retrieving the value of a capture group from a regular expression. I found server-side extensions ( lib_mysqludf_preg ) that would add this functionality, but I cannot install this extension in my environment.

So, I'm looking for a way to simulate capturing part of a regular expression as a column in an SQL query.

My data looks like this (and I cannot 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          |
+-----------------------------+

I am looking to capture each of the last 4 digits from each line. Numbers are always the last part of the meaning, and they are always separated by dots. The following regex will match all the values ​​I want:

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

, , , where, .

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

Answer 1

, , . , , . version1 2- , , .

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
;