-2

Suppose I have a MySQL table which has two rows and two columns.

Column 1 = ID
Column 2 = NUMBERS

NUMBERS field in first row & second row has following comma separated value:

NUMBERS(Row 1) = 1,2,3,4,5,6,7,8
NUMBERS(Row 2) = 6,7,8,9,10,11

Now I need to find all the numbers which are between 1 & 11. I have tried MYSQL BETWEEN function but it has not returned desired results.

Is there a way I can get desired result?

peterh
  • 11,875
  • 18
  • 85
  • 108
Muhammad Junaid Iqbal
  • 111
  • 1
  • 1
  • 7
  • 1
    Go through this [link](http://stackoverflow.com/questions/11835155/mysql-split-comma-separated-string-into-temp-table) Since Mysql is not bundled with Split string function you have to do it on your own. Once you have splitted the number then use BETWEEN clause to fetch the desired result from temp table created in above link of example – Ashu Jha Aug 25 '16 at 10:43
  • One way: Showing all the calculations: http://sqlfiddle.com/#!9/cfea18/6. Explanation: [Can I resolve this with pure mysql? (joining on ';' separated values in a column)](http://stackoverflow.com/a/33806675/3184785) – Ryan Vincent Aug 25 '16 at 14:43
  • 1
    Now you know that you can't use a relational database as a text file, don't you? You create rows in SQL, you don't separate data using commas, this isn't javascript. – Mjh Aug 25 '16 at 14:52

1 Answers1

0

Having a table with sequence numbers like

Table sequence_numbers

number
     1
     2
    ..
   100

you can extract the numbers from your list using a JOIN and FIND_IN_SET() function:

select t.ID, s.number
from mytable t
join sequence_numbers s
  on find_in_set(s.number, t.NUMBERS)

Result:

| ID | number |
|----|--------|
|  1 |      1 |
|  1 |      2 |
|  1 |      3 |
|  1 |      4 |
|  1 |      5 |
|  1 |      6 |
|  2 |      6 |
|  1 |      7 |
|  2 |      7 |
|  1 |      8 |
|  2 |      8 |
|  2 |      9 |
|  2 |     10 |
|  2 |     11 |

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

Now many tasks are much simpler with that result.

Limit to numbers from 3 to 7:

select t.ID, s.number
from mytable t
join sequence_numbers s 
  on find_in_set(s.number, t.NUMBERS)
where s.number between 3 and 7

Result:

| ID | number |
|----|--------|
|  1 |      3 |
|  1 |      4 |
|  1 |      5 |
|  1 |      6 |
|  2 |      6 |
|  1 |      7 |
|  2 |      7 |

http://sqlfiddle.com/#!9/b4144/3

A simple way to create a big sequence table is to use the internal columns table (or any table with enough rows) joining it with itself:

create table sequence_numbers (number int auto_increment primary key)
  select null as number
  from information_schema.columns c1
  cross join information_schema.columns c2
  limit 1000000
;

http://sqlfiddle.com/#!9/7cc0a/2

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53