3

I've got this Regex that works fine in all my testing, but I am unable to make it work in MySQL, despite the examples that I've been googling and trying variations of this:

^[A-Za-z]{2,4}-\d{3}-\d{2}$

In Javascript, it correctly matches AA-001-01 through ZZZZ-999-99, and so have tried it in MySQL with various tweaks to the regex, all with no luck:

SELECT * FROM products WHERE sku REGEXP '^[A-Za-z]{2,4}-\d{3}-\d{2}$'

(The table contains thousands of records where sku matches the samples given above)

As best as i can tell, Mysql Regex support is limited, but should this not work properly, or is there some further syntax consideration to work with Mysql?

GDP
  • 8,109
  • 6
  • 45
  • 82

1 Answers1

8

To match a digit you should use either [0-9] or [[:digit:]].

Try this:

SELECT col1, col2, ..., coln
FROM products
WHERE sku REGEXP '^[A-Za-z]{2,4}-[0-9]{3}-[0-9]{2}$'

See it working online: sqlfiddle.

See the manual for REGEXP.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Man, my head spins at the mere mention of Regex, and I spend WAY too much time trying to figure it out because when I see it done, I want to slit my throat because it's so DAMN easy......thank you good sir! – GDP Aug 08 '12 at 22:19