1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL LIKE search (blech!) with RegEx?

Discussion in 'General PHP and MySQL Discussions' started by Jaxel, Mar 26, 2014.

  1. Jaxel

    Jaxel Well-Known Member

    Okay, lets say I have a table with the following rows:
    1 - apat
    2 - vio,apat,pat
    3 - pat
    4 - vio,apat
    5 - pat,vio
    6 - pat
    Lets say I want to do a MySQL like search, to get only rows where the column contains "pat". I would do this:
    SELECT * FROM table WHERE column LIKE '%pat%'
    The problem here is that would return all 6 rows, since technically the word "apat" contains "pat". What I would like to do, is a search query where only rows 2, 3, 5 and 6 would be returned... since rows 1 and 4 contain "apat" and not "pat", I would prefer them excluded. What would be the best way to do this?

    Is it possible to do a regex? Could I search for '[^\w]pat[^\w]'? The [^\w] class would match any character that is not a word character; which would include commas, as well as start and end positions.
  2. RobParker

    RobParker Well-Known Member

  3. Jaxel

    Jaxel Well-Known Member

  4. Adrian Schneider

    Adrian Schneider Active Member

    Seems like a case for data normalization to me.

    Storing ordered lists in a string where you want special filtering and ordering is going to be a pain in the butt, and probably won't scale that well. Why not store it in a secondary table where you can query what you want, and even set ordering or any other relevant data you have?

    I mean, you can probably get it to work with a regular expression (or two - one inside your order clause), but that's really not the proper way.
  5. Mike

    Mike XenForo Developer Staff Member

  6. Jaxel

    Jaxel Well-Known Member

    FIND_IN_SET was exactly what I was looking for... thanks.

Share This Page