PeterL PeterL - 1 month ago 14
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]+$'


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.

Answer

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.