find substring in string but not follow orders

Question

I got list of data like this

Data	Set
1,2,3,4	A
2,3,4	B
1,4	C
2,4	D

These scenario in mysql should match:

Find Data	Matched Set
1,2	A
1,4	A , C
2,4	A, B, D
1,3,4	A

Luckily that the lists from Data is ALWAYS INT and sort by ascending.

you should take a look at https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and think about normalisation — nbk, Oct 25 '21 at 17:50
@nbk: I know, but following multiselect type of magento2, they stored the data like that — Nam TU Hai, Oct 25 '21 at 19:41

score 0 · Answer 1 · answered Oct 25 '21 at 17:34

Assuming you are only looking for one of your "Find Data" results in a given query, you can use LIKE for this. But you need some delimiter before and after each number for that to work; I'm going to use ;. So to find "1,2":

select group_concat(Set order by Set) as 'Matched Set'
from list_of_data
where concat(';',replace(Data,',',';;'),';') LIKE '%;1;%;2;%';

Here we change each row's data; for instance, 1,3,4 becomes ;1;;3;;4;. Then search for the delimited numbers you are trying to find, with % allowing other numbers before or after each one. If you cannot translate the 1,2 to this form in your code, you can do it in your query, like:

LIKE concat('%;',replace('1,2',',',';%;'),';%')

find substring in string but not follow orders

1 Answers1