2

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.

Casimir et Hippolyte
  • 88,009
  • 5
  • 94
  • 125
  • The `(?=<...>)` clause in your expressions is called a positive lookahead. You're usually not allowed to use repetition operators like `*` within these. Is your requirement to match only those strings that are composed of just alphabets and a hyphen? – Chitharanjan Das Oct 20 '16 at 09:27
  • Yes, MET-[A-z] (so, only alphabets and a hyphen), and then MET-[A-z0-9] (alphabets, a hyphen and numbers). 2 different queries. –  Oct 20 '16 at 10:30

1 Answers1

1

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.
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • Thing that I forgot to include, records have 'WCP-', etc, so I only want to pull MET-... I just added MET after the ^'s. Apologies and thanks –  Oct 20 '16 at 10:59
  • See [`^MET-[-[:alnum:]]*([-[:alpha:]][-[:alnum:]]*[0-9]|[0-9][-[:alnum:]]*[-[:alpha:]])[-[:alnum:]]*$`](https://regex101.com/r/el5Qv8/2) and [`^MET-[-[:alpha:]]*$`](https://regex101.com/r/el5Qv8/3), just add `MET-` (and in the second one, replace `+` with `*`). – Wiktor Stribiżew Oct 20 '16 at 11:01
  • Also, as for `A-z`, see [*Why is this regex allowing a caret?*](http://stackoverflow.com/a/29771926/3832970) – Wiktor Stribiżew Oct 20 '16 at 11:04