mysql alternative regex - return the part of regular expression which matched the sample-CodePudding

trying to find a way in mysql how to return the part of regular expression which matched the test string.

regex: (^123[0-9] $)|(^9876[0-9] $)|(^56789012$)

test string: 123456

I want to get: ^123[0-9] $

Many thanks.

CodePudding user response：

I don't know any comand that can do it

but you can always

do it like this

SELECT '123456' REGEXP '(^123[0-9] $)|(^9876[0-9] $)|(^56789012$)'

| '123456' REGEXP '(^123[0-9] $)|(^9876[0-9] $)|(^56789012$)' |
| ----------------------------------------------------------: |
|                                                           1 |

SELECT CASE
WHEN '123456' REGEXP '^123[0-9] $' THEN '^123[0-9] $'
WHEN '123456' REGEXP '^^9876[0-9' THEN '^9876[0-9'
WHEN '123456' REGEXP '^56789012$' THEN '^56789012$'
ELSE
'no pattern'
END

| CASE
WHEN '123456' REGEXP '^123[0-9] $' THEN '^123[0-9] $'
WHEN '123456' REGEXP '^^9876[0-9' THEN '^9876[0-9'
WHEN '123456' REGEXP '^56789012$' THEN '^56789012$'
ELSE
'no pattern'
END |
| :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ^123[0-9] $                                                                                                                                                                             |

db<>fiddle here

CodePudding user response：

Here's a solution:

SELECT 'the 1st one' as which_regexp WHERE '123456' REGEXP '^123[0-9] $'
UNION
SELECT 'the 2nd one' as which_regexp WHERE '123456' REGEXP '^9876[0-9] $'
UNION
SELECT 'the 3rd one' as which_regexp WHERE '123456' REGEXP '^56789012$';