0

I have this issue that i'm stuck in...

I have a field like an array, values separated by space. Let's say we have these values:

v1: "100 150 200 300"
v2: "50 100"
V3: "130 230 415"
v4: "340 500 600"
...

Then i have two user input values like a min and a max, let's say 120 and 300. I need to select with mysql from table all rows that have an array value between these min and max

So the output should be v1 and v3 from the previous example.

Can someone help me, please?

NullUserException
  • 83,810
  • 28
  • 209
  • 234
chitoiu daniel
  • 107
  • 2
  • 13
  • 6
    The difficulty of this should teach you not to put array values in a relational database. Normalize your schema and the problem is easy. – Barmar Nov 13 '13 at 18:38
  • This is a terrible practice. Do relationships [the right way](http://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de/7296873#7296873) and you won't have [these problems](http://stackoverflow.com/questions/7364803/storing-arrays-in-the-database/7364834#7364834). – NullUserException Nov 13 '13 at 18:42
  • you need to go for some type of UDF – Santhosh Nov 13 '13 at 18:50
  • As i told @popovitsj it's a big full database that it's difficult to change. And the array fields can contain from 1 to 10 elements.. – chitoiu daniel Nov 14 '13 at 12:28

2 Answers2

0

I completely agree with the comments saying that this is terrible design, but I think this could be solved with a function that splits your array into its respective values, combined with an APPLY.

This should then yield a table like

v1 100

v1 150

etc.

v2 50

v2 100

etc.

From there the problem should be easy.

wvdz
  • 16,251
  • 4
  • 53
  • 90
  • i know, but it's too late for this now, i have a big database full with array values, and the array can contain from 1 to 10 elements... so i really need a formula to deal with this inside the select... – chitoiu daniel Nov 14 '13 at 06:50
-1

I would check this thread Split value from one field to two

Or pull the data back and use server-side code to split the value into an array and then do the comparison.

Community
  • 1
  • 1
heathhettig
  • 115
  • 7
  • yes, this would be the last solution if i can't find a formula to deal the issue in select statement. But my code will get too bulky... – chitoiu daniel Nov 14 '13 at 12:30