mysql 正则匹配 捕获组,在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 |

+-----------------------------+

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

;