Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

SQL SELECT query for multiple values and ranges in row data of a given column.

Problem description:

Server: MySQL
Database: Customer
Table: Lan
Column: Allowed VLAN (in the range 1-4096)

One row has data as below in the column Allowed VLAN:
180,181,200,250-499,550-811,826-mismatched

I need a SELECT statement WHERE the column Allowed VLAN includes a given number for instance '600'. The given number '600' is even one of the comma separated value or included in any of the ranges "250-499","550-811" or it is just the starting number value of "826-mismatched" range.

SELECT * WHERE `Allowed VLAN`='600' OR `Allowed VLAN` LIKE '%600%' OR (`Allowed VLAN` BETWEEN '1-1' AND '1-4096');

I could not figure it out how to deal with data ranges with WHERE Clause. I have solved the problem with PHP code using explode() split functions etc., but I think there are some SQL SELECT solutions.

I would be appreciated for any help.

share|improve this question
1  
The best advice that I can give you is to fix your data structure. The allowed values should be in a separate table, with a different row for each range or individual value. – Gordon Linoff May 22 at 14:26
Yes this is correct, the data structure is weird. The database design is not mine, coming from customer side and could not be sure if they will change it. – user2409597 May 23 at 0:24
add comment (requires an account with 50 reputation)

1 Answer

up vote 0 down vote accepted

I would highly recommend normalizing your data. Storing a comma-separated list of items in a single row is generally never a good idea.

Assuming you can make such a change, then something like this should work for you (although you could consider storing your ranges in different columns to make it even easier):

create table lan (allowedvan varchar(100));

insert into lan values 
  ('180'),('181'),('200'),('250-499'),('550-811'),('826-mismatched');

select *
from lan
where allowedvan = '600'
  or 
    (instr(allowedvan,'-') > 0 and 
     '600' >= left(allowedvan,instr(allowedvan,'-')-1) and
     '600' <= right(allowedvan,length(allowedvan)-instr(allowedvan,'-')) 
      )

SQL Fiddle Demo

This uses INSTR to determine if the value contains a range (a hyphen) and then uses LEFT and RIGHT to get the range. Do not use LIKE because that could return inaccurate results (600 is like 1600 for example).

If you are unable to alter your database, then perhaps look into using a split function (several posts on it on SO) and then you can do something similar to the above method.

share|improve this answer
Thank you @sgeddes for this solution, the database design is coming from customer side so not sure if they will follow why to change data structure. Definitely I have to try to mix this solution with the split function. Also, thanks mentioning the LIKE misuse. – user2409597 May 23 at 0:09
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.