PeterL PeterL - 1 year ago 85
MySQL Question

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


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.

Answer Source

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

You may use



  • ^ - 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



  • ^ - matches the start of string
  • [-[:alpha:]]+ - matches 1 or more - or letters (=alpha chars)
  • $ - end of string.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download