#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Los Gatos, CA
    Posts
    42
    Rep Power
    18

    MySQL REGEXP (non-greedy)??


    MySQL REGEXP (non-greedy)??
    i want to construct a non-greedy REGEXP match in mysql.

    let's say i have a column "cat" which has data like:

    Sports / Football
    Sports / Football / Players
    Sports / Football / Coaches
    Sports / Baseball
    Sports / Baseball / Players
    Sports / Baseball / Coaches


    I only want to grab the 2nd level (not the 3rd ...nth levels):
    Sports / Football
    and Sports / Baseball

    I've tried to write a REGEXP statement which finds data excluding "/"...However it doesn't seem to work:


    I tried:
    SELECT * FROM CATEGORIES WHERE CATEGORY REGEXP 'Sports /(.*)?/';

    ...this REGEXP would work in perl, but not MYSQL.

    I also tried something to this effect, but to no avail:

    SELECT * FROM CATEGORIES WHERE CATEGORY REGEXP 'Sports /([^/]*)';


    Please help!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Los Gatos, CA
    Posts
    42
    Rep Power
    18
    I figured out the REGEXP solution (albeit slow):


    SELECT * FROM CATEGORIES WHERE CATEGORY REGEXP '^Sports /[^/]+$';


    However, a much Faster solution involves SUBSTRING_INDEX:

    SELECT CATEGORY FROM CATEGORIES WHERE CATEGORY LIKE 'Sports%' GROUP BY SUBSTRING_INDEX(CATEGORY, '/', 2);

IMN logo majestic logo threadwatch logo seochat tools logo