How to convert this regex to mysql posix

Please forgive me if this has answers already.

Being a total n00b with regex, I had a tough time getting good regex for this scenario:

I need to find if a string contains just letters, or letters and numbers from a MySQL table field...... eg. just get MET-KL2531910 and just get MET-IHLPOUJ without numbers (so MET-KL2531910 will not be included).

After some time, came up with these:

For MET-KL2531910

REGEXP '^(?=.*[-a-zA-Z])(?=.*[0-9])[-A-z0-9]+$'

For MET-IHLPOUJ

REGEXP '^(?=.*[-A-z])[-A-z]+$'

However, since MySQL uses POSIX, these come out with this error

Got error 'repetition-operator operand invalid' from regexp

Can anyone convert my regexes to POSIX regexes please.

Solution

For MET-KL2531910
REGEXP '^(?=.*[-a-zA-Z])(?=.*[0-9])[-A-z0-9]+$'

You may use

^[-[:alnum:]]*([-[:alpha:]][-[:alnum:]]*[0-9]|[0-9][-[:alnum:]]*[-[:alpha:]])[-[:alnum:]]*$

Details:

^ - start of string
[-[:alnum:]]* - 0+ hyphens or alphanumeric chars
( - an alternative group:
- [-[:alpha:]][-[:alnum:]]*[0-9] - a hyphen/alpha char, 0+ alnum/- chars, a digit
- | - or
- [0-9][-[:alnum:]]*[-[:alpha:]] - a digit, 0+ alnum/- chars, hyphen/alpha char
) - end of the alternation group
[-[:alnum:]]* - 0+ hyphens or alphanumeric chars
$ - end of string.

For MET-IHLPOUJ REGEXP '^(?=.*[-A-z])[-A-z]+$'

Here, you do not need the lookahead at all since you only match what you require, use

^[-[:alpha:]]+$

Here,

^ - matches the start of string
[-[:alpha:]]+ - matches 1 or more - or letters (=alpha chars)
$ - end of string.