SQL query to select dates between two dates where dates are stored with commas

Question

I'm using Multidatespicker so I have dates in my database as follow:

ID    POST  KEY                  VALUE
1     1     multipledates        20151020, 20151015, 20151101
2     2     multipledates        20151103
3     3     multipledates        20151022, 20151010, 20151202, 20160410
...

Then I have 'date_start' and 'date_end'. I need to select dates between this dates.

This SQL query only works in my example with ID2 (because it hasn't commas)

SELECT ... BETWEEN $date_start AND $date_end

The solution would be to use something like 'LIKE' but not if possible:

SELECT ... BETWEEN LIKE '%$date_start%' AND LIKE '%$date_end%'

Any ideas?

Thanks.

(The obvious thing it to change the table so that it has only value per row) — Alex K., Oct 15 '15 at 16:24
The logical solution would be use single values for data but I wonder if it would be possible — kurtko, Oct 15 '15 at 16:25
More accurately, the correct thing to do would be to normalize your data. — Strawberry, Oct 15 '15 at 16:26

score 3 · Accepted Answer · edited May 23 '17 at 12:03

You should redesign your schema for sure.

But just as a study case you can:

http://sqlfiddle.com/#!9/22755/4

SELECT t.id,
   t.date
FROM (SELECT
  t1.id,
  DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.value, ',', numbers.n), ',', -1)) `date`
FROM
  numbers 
  INNER JOIN t1
  ON CHAR_LENGTH(t1.value)
     -CHAR_LENGTH(REPLACE(t1.value, ',', ''))>=numbers.n-1
ORDER BY
  id, n ) t
WHERE t.date BETWEEN $date_start AND $date_end

Notice this solution expects that you have numbers table with record values from 1 to max number of elements in your valuelist column.

(c) code based on https://stackoverflow.com/a/17942691/4421474

SQL query to select dates between two dates where dates are stored with commas

1 Answers1