Gossamer Forum
Home : General : Databases and SQL :

mySQL REGEXP

Quote Reply
mySQL REGEXP
Okay, I have a mySQL database (dstructure) that consists of the DMOZ category structure. I would like to limit SELECT of dstructure.name field (category path - for example, Top/Arts/Animation) to xx number subcats. For example, if end-user selects {Depth} as 1 and the {Top Category} as Arts, then it should produce the following results:

Arts/
Arts/Animation
Arts/Architecture
etc.

I tried this (dynamically built depending on {Depth} which in this example is 1 - Top ignored):

SELECT * FROM `dstructure` WHERE `name` REGEXP CONVERT( _utf8 '^Top/Arts' USING latin1 ) AND `name` REGEXP CONVERT( _utf8 '^(.*)+(/(.*)+)?(/(.*)+)?$' USING latin1 )

...but no good. The mySQL REGEXP is too greedy, and {Depth} is ignored. Easy to see why, but a solution escapes me. Any ideas?



----
Cheers,

Dan
Founder and CEO

LionsGate Creative
GoodPassRobot
Magelln
Quote Reply
Re: [dan] mySQL REGEXP In reply to
Try using [^/]+ instead of .* for your category name matches.

Adrian
Quote Reply
Re: [brewt] mySQL REGEXP In reply to
Quick reply =) Not sure I follow. How would the SELECT statement look then?

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
GoodPassRobot
Magelln
Quote Reply
Re: [dan] mySQL REGEXP In reply to
Something like:

SELECT * FROM `dstructure` WHERE `name` REGEXP CONVERT( _utf8 '^Top/Arts' USING latin1 ) AND `name` REGEXP CONVERT( _utf8 '^([^/]+)(/([^/]+))?(/([^/]+))?$' USING latin1)

Which really, you could combine those two conditions into one: ^Top/Arts(/[^/]+)?$

Adrian
Quote Reply
Re: [brewt] mySQL REGEXP In reply to
Yes, that appears to work! Thanks =) And yes, not sure why I used two REGEXP's. I only really looked at the full SELECT statement today, and that stood out. I've combined them into one - given that the table is almost 100 MB, probably good idea =)

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
GoodPassRobot
Magelln