Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: RegExp Pattern for a Tag Column

    Hi

    I have a column named Tags of varchar type which hold tags separated by commas.

    Example of a possible field value for Tags: bikes, road trip, summer

    Code:
    SELECT * FROM `tablename` WHERE `Tags` LIKE '%road%'
    will obviously select the example row but it shouldn't since it doesn't come under road but under road trip.

    Using PHP's preg_match is easy but in this case I need a SQL solution.

    How do I go abt making the condition for the query using the Regexp (^|,\s*)road(\s*,|$) ?

    Now I got this to work and it doesnt select the road trip row for road, but neither does it get selected for road trip
    Code:
    (^|,\s*)road trip(\s*,|$)
    Any idea what I might be dong wrong here ?

    Thanx
    MySQL 5.1

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure there are no extraneous spaces around your commas, and then do this:

    ... WHERE CONCAT(',',Tags,',') LIKE '%,road,%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Nice workaround, but having to change the current set of data to get trim whitespaces surrounding the commas is not viable.

    Anyway, MySQL doesnt seem to recognize \s - I just replaced it with a whitespace and it worked !

    Code:
    (^|, *)road trip( *,|$)
    MySQL 5.1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, if every comma is consistently followed by a space, then you can simply do it like this --
    Code:
    ... WHERE CONCAT(', ',Tags,', ') LIKE '%, road, %'
    see the difference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Problem is, the tag information was entered by users via an html form (were told to separate tags by a comma) which is still online and I just can't rely on the data entered by them. Obviously, the back-end script would be modified to strip spaces surrounding commas, but for the existing data, I'll need to assume any number of spaces surrounding a comma.
    MySQL 5.1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •