在MySQL中模拟正则表达式捕获组

Question

据我所知，MySQL不支持从正则表达式匹配中检索捕获组的值。我发现了一个服务器端扩展(lib_mysqludf_preg)，它可以添加此功能，但我将无法在我的环境中安装此扩展。

因此，我正在寻找一种方法来模拟在SQL查询中将正则表达式匹配的一部分作为列进行捕获。

我的数据看起来像下面这样(我无法更改服务器上的数据格式)：

+-----------------------------+
| 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          |
+-----------------------------+

我想捕获每一行的最后4个数字。这些数字总是该值的最后部分，它们总是由点分隔的。以下正则表达式将匹配我想要的所有值：

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

我希望得到一些能够将每个数字作为列捕获的函数组合，以便我可以在查询的where子句中使用该数字，并能够获取版本号。

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

- Alex Q

---

为什么不选择你喜欢的编程语言来完成这个任务呢？ - Qtax

至少你可以通过 SUBSTRING_INDEX(ver, '.', -1) as version4 轻松获取最后一个数字。其他数字可能需要使用 MySQL 字符串函数 进行操作。不知道是否有更好的方法而无需安装扩展。 - Qtax

Answer 1

经过一些尝试，我得出了以下查询语句，可以满足我的需求。基本上，我会将数字从字符串的末尾分离出来，然后删除该数量的字符，再分离下一个数字。版本1列仅限于正的两位数，但这在我的情况下是可以接受的限制。

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
;

- Alex Q