MySQL Regex LIKE

Question

I have a column following this pattern:

XXXXX-and_some_text

where XXXXX can be a number from 3 to 5 digits followed by a dash. A regex like that:

\d{3,5}-

will find same rows as LIKE "XXX%"

I want to find rows which match exactly, f.e.

looking for 903 should find 903-Text and not 9030-Other_Text or 90344-Another-Text.

I could filter the rows afterwards in my php code but it would be much nicer to accomplish that in one single sql statement.

Any ideas?

Answer 1

Here are a few methods:

where col like 'XXX-%'
where col regexp '^XXX-'
where substring_index(col, '-', 1) = 'XXX'

The advantage of the first method is that it might use an index on the column, if available.