Using REGEXP in MySQL

daymobrew

I am trying to use REGEXP in MySql to select specific rows in a table.

My table contains a list of Dublin Bus timetables (for my BusSched app) with the route number as a unique column.
I've read the Selecting Particular Rows page of the MySql docs and experimented.

I have something working but it incorrectly matches more rows than it should.

SELECT route,tbl.from,tbl.to FROM tbl WHERE route REGEXP '^237|37|84$'

This matches 237, 37, 37X and 84. The 37X route is not wanted.

If I change the regexp to '^37|84|237$' it matches 237, 37, 37X, 84 and 84X. The 'X' routes are not wanted.

How to I exclude the routes that I don't want?
My SELECT statement will list the routes I want to match, and only them.

I realise that I could expand the statement e.g. "route='37' OR route='84' OR route='237'" but REGEXP is nice and concise.

seamus

I'm not familiar with SQL regexp, but is it possible to change it to
'^[237|37|84]$'
?

daymobrew

seamus wrote:

I'm not familiar with SQL regexp, but is it possible to change it to
'^[237|37|84]$'
?

That is what I first tried but got the empty set returned. I didn't try using round brackets i.e. '^(237|37|84)$', and can't right now as the db is on my home computer.

seamus

Quick test with a dummy DB and sample data says that yes, round brackets as you've said will do the trick. Only returned the three routes for me.

daymobrew

seamus wrote:

Quick test with a dummy DB and sample data says that yes, round brackets as you've said will do the trick. Only returned the three routes for me.

Cool, thanks.
A few mins ago I found the MySQL page with regex examples and it shows round brackets.