在mysql中模拟正则表达式捕获组 [英] Simulating regex capture groups in mysql

问题描述

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

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.

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

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

我正在寻找每行的最后4位数字.数字始终是值的最后一部分，并且始终由点分隔.以下正则表达式将匹配我想要的所有值:

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:]]+$

我希望得到的结果是一些功能组合，这些功能可以将每个数字捕获为一列，这样我就可以在查询的where子句中使用该数字，并能够取回版本号.

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;

推荐答案

经过反复试验，我想到了以下查询来满足我的需要.基本上，我将数字从字符串末尾分离出来，然后在分离下一个数字之前删除那么多字符.在version1列中，最多只能使用2位正数，但这是我可以接受的限制.

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
;

这篇关于在mysql中模拟正则表达式捕获组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！