Comparing values from a string in a MySQL query

Posted by bellesebastien on Stack Overflow See other posts from Stack Overflow or by bellesebastien
Published on 2010-03-18T19:01:34Z Indexed on 2010/03/18 19:21 UTC
Read the original article Hit count: 381

Filed under:

mysql

|

multiple-value

|

comparison

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.

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about multiple-value

select & print multiple value from dropdown list

as seen on Stack Overflow - Search for 'Stack Overflow'
hi , in this code how can selected multiple value from dropdown list <?php include ("connect.php"); $member_id = intval($_POST['sector_list']); if($member_id == 0) { // Default choice was selected } else { $res = mysql_query("SELECT * FROM members WHERE MemberID = $member_id… >>> More
How can I create a Multiple Value Combo box on an Unbound Form

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi I need your help on MS Access 2007. I have a big problem with my MS Access Unbound Form. How can I create a combo box i do not want bound to a table, show a multiple value List? The Combo is named Sector and want these values to be selected in Multiples 9Which should be possible in Access 2007)… >>> More
How to get multiple value of checkbox?

as seen on Stack Overflow - Search for 'Stack Overflow'
I has checkbox the list of media in my webpage <input name="c1" type="checkbox" value=<%= item.MediaId %> />> mediaA <input name="c1" type="checkbox" value=<%= item.MediaId %> />> mediaB <input name="c1" type="checkbox" value=<%= item.MediaId %> />>… >>> More
how to bind multiple value in WPF ??

as seen on Stack Overflow - Search for 'Stack Overflow'
hai am using the below binding to bind my value 'Name' to textblock1. <TextBlock Text="{Binding Name}" /> now the problem is i want to bind another value called 'ID' with that same textblock1 is it possible to bind value like using Name + ID like that??? :) >>> More
how to select multiple value from drop down and how to read those values

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi All, how to implement selecting multiple values from drop down and how to read those values? >>> More