Mysql filter query where condition

Question

I have 2 columns like

name    price
a|b|c   100|200|150
x|y|z   150|100|160

Here I want to get price where price>150. I don't know how to explode? Thanks

http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query http://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql — Suresh Kamrushi, Nov 26 '13 at 14:25
Hello, Jaywalker anti-pattern! Instead of trying to deal with delimiter-separated values when creating your query, you should split your values and store them in linked table - and that will resolve all the issues once and for all — Alma Do, Nov 26 '13 at 14:40

score 1 · Accepted Answer · answered Nov 26 '13 at 14:26

You can't explode in MySQL. I would strongly suggest that you restructure your table to be relational (i.e. each name and price should occupy a single row).

It may be possible to do some type of numerical comparison via substrings, but I think it would be much easier to just query the entire table and filter the results via php (I assume you're using php since you said explode, which is normally called split in other languages).

foreach ($rows as $row) {
    $names = explode("|", $row["name"]);
    foreach (explode("|", $row["price"] as $idx => $price) {
        if ($price > 150) {
            $all_names[] = $name[$idx];
        }
    }
}

This of course assumes that name/price column indices align properly. It is also costly since it has to query the entire table and does not use any (MySQL) indices.

To normalize the data, what you could do is have a table called Groups that has a groupID. Then you have your current table with GroupID, name, price tuple. Your data would look like this:

groupID    name    price
1          a       100
1          b       200
1          c       150
2          x       150
2          y       100
2          z       160

Then you can simply check WHERE price > 150, which would be extremely fast if price were indexed. It would also allow you to search by group -- this could be useful and even faster in some circumstances.

To set up the above table based on what you already have, you will have to query the whole table and then explode the name/price and insert by the corresponding index with the existing group ID.

Mysql filter query where condition

1 Answers1