sibipi sibipi - 24 days ago 6
MySQL Question

MySQL REXEXP doesn't work propertly

SELECT 'pharase' REGEXP '[^a-zA-Z]+'
- it verifies that the phrase does not contain English chars (or not?).

Why is query
SELECT '123g' REGEXP '[^a-zA-Z]+' -> 1
show 1 instead 0?

Answer

To check if a string only contains English chars, use

SELECT 'pharase' REGEXP '^[a-zA-Z]+$'

where:

  • ^ - asserts the position at the start of the string
  • [a-zA-Z]+ - 1 or more ASCII letters
  • $ - the end of string

SELECT '123g' REGEXP '[^a-zA-Z]+' -> 1 shows 1 because the string contains 123, non-letters, a partial match is found with REGEXP (unlike LIKE that requires a full string match).