Comparing values from a string in a MySQL query

Question

I'm having some trouble comparing values found in VARCHAR fields.

I have a table with products and each product has volume. I store the volume in a VARCHAR field and it's usually a number (30, 40, 200..) but there are products that have multiple volumes and their data is stored separated by semicolons, like so 30;60;80.

I know that storing multiple volumes like that is not recommended but I have to work with it like it is.

I'm trying to implement a search by volume function for the products. I want to also display the products that have a bigger or equal volume than the one searched. This is not a problem with the products that have a single volume, but it is a problem with the multiple volume products.

Maybe an example will make things clearer: Let's say I have a product with this in it's volume field: 30;40;70;80. If someone searched for a volume, lets say 50, I want that product to be displayed.

To do this I was thinking of writing my own custom MySQL function (I've never this before) but maybe someone can offer a different solution.

I apologize for my poor English but I hope I made my question clear.

Thanks.

Answer 1

You only need to compare with the largest value. Are the volumes always stored in increasing order from left to right? If so then compare with the value after the last semicolon if one is present. You can use SUBSTRING_INDEX to select the last value.

SELECT *
FROM products
WHERE CAST(SUBSTRING_INDEX(volume, ';', -1) AS UNSIGNED) >= 50