Mysql intersect two strings

Question

I have the following tables:

TableFinal

column id, with first row having value 1
column numbers, with first row having value `1,5,6,33,2,12,3,4,9,13,26,41,59,61,10,7,28`

And

TablePick

column id, with first row having value 1
column numbers, with first row having value 2,12,26,33

I want to check if the numbers from TablePick, column "selected" are contained in the column "numbers" of TableFinal.

I have to mention that in TablePick, the numbers in column "selected" are ordered ASC, while in TableFinal, the numbers in column "numbers" are shuffled.

Usually I would put each of these in an array using PHP and then intersect the 2 arrays and count the resulted array. But in MYSQL, it is not that simple, so practically I have no idea where to start.

Maybe I should create an ARRAY_INTERSECT function? Or do we have a simpler solution?

SELECT * FROM TablePick p RIGHT JOIN TableFinal f ON f.id=p.id WHERE ARRAY_INTERSECT(p.selected,f.numbers)

MySQL is not a panacea. Normally, this kind of analysis is done (I think) in application layer code. — Strawberry
– Strawberry, Commented Jan 27, 2014 at 18:52
here is a similar question with an accepted answer at the bottom. stackoverflow.com/questions/18629176/… — nisav
– nisav, Commented Jan 27, 2014 at 18:54
I suspect that data normalization can help to avoid such problems — mlt
– mlt, Commented Jan 27, 2014 at 18:56
What is the maximum number of values in a row in either table? — Gordon Linoff
– Gordon Linoff, Commented Jan 27, 2014 at 19:09

Eugen Rieck · Accepted Answer · 2014-01-27 19:00:13Z

1

Sorry to say so, but your schema needs some serious maintenance: NEVER EVER store more than one information in one field, if you need to access them separately.

You need a pair of join tables, where instead of the first row (1, "1,5,6,33,2,12,3,4,9,13,26,41,59,61,10,7,28") you have the rows

(1,1)
(1,5)
(1,6)
(1,33)
...

and instead of the row (1, "2,12,26,33") you have the rows

(1,2)
(1,12)
(1,26)
(1,33)

Now you query is simply:

SELECT ... FROM TableFinal 
INNER JOIN TABLE TablePick ON TableFinal.number=TablePick.number
WHERE TableFinal.id=1
AND TablePick.id=1

EDIT

Please understand, that even if this were possible without MySQL abuse, it would be a performance killer, once the number of rows start to rise: We are talking of n*m array intersects, if the tables have n and m rows respectivly.

answered Jan 27, 2014 at 19:00

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NVG Over a year ago

Well, the example is not identical to my db, just somehow similar to my requirement. I was only looking for some way to intersect two strings like intersecting 2 arrays

jean Over a year ago

It sounds like you are in need of some sort of substring logic to fetch some data from mess of DB where, otherwise with a good normalization, you can easily and efficient fetch the same data just using a couple of joins

Collectives™ on Stack Overflow

Mysql intersect two strings

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related