mysql regexp some of these chars + 1 unknown char

user_4870273 0
Viewed 49

This regexp bellow means that word must be only of (some) letters of ABC. so A matches, AA, AB, BA

word REGEXP '^[ABC]*$'

but how to make it some of ABC letters and one any uknown letter.

mysql regex string
2 Answers

To match a string with just A, B, C letters and one any letter, you can use

REGEXP '^[ABC]*([A-Z][ABC]*)?$'

To match a similar string of A, B and C with any two letters, you can use

REGEXP '^[ABC]*([A-Z][ABC]*){0,2}$'

since ? is basically {0,1} quanitifier. See the regex demo #1 and regex demo #2.

NOTE: If you do not want the additional letters to be A, B or C, replace [A-Z] with [D-Z].

Details

  • ^ - start of string
  • [ABC]* - zero or more A, B or C chars
  • ([A-Z][ABC]*)? - an optional (one or zero, if {0,2} is used, then zero, one or two) occurrence of any ASCII letter followed with zero or more A, B or C chars
  • $ - end of string
user_38329700

Requiring one letter other than ABC:

x REGEXP '^[ABC]*[D-Z][ABC]*$'

Allowing one letter other than ABC:

x REGEXP '^[ABC]*[D-Z]?[ABC]*$'

Length restriction:

... AND CHAR_LENGTH(x) > 3
... AND CHAR_LENGTH(x) BETWEEN 2 AND 5
(etc)
user_17668310
Related