mysql search for multiple substring in single column

Question

I need an efficient way to search multiple sub string in same column of a table. Following is the sample table and inefficient query I create. Can any one help me to make it more dynamic.

id | column_2 | column_2
1  | 65,35    | 14,13,20
2  | 41,15,16 | 10,21,23
3  | 12,14,15 | 10,12,20

SELECT * FROM `table1` WHERE `column_2` LIKE '%10%' AND `column_2` LIKE '%23%';

These values will be lengthy and the and condition also will be dynamic in amount. So is there any efficient way to create query for remove and condition (having).

Answer 1

I took the function SPLIT_STR from here: MYSQL - Array data type, split string,

Our data-set

Assume we have the following data set;

select * from a_table;
+----+-------------+
| id | column2     |
+----+-------------+
|  1 | 10,11,23    |
|  2 | 5,14,23     |
|  3 | 2,18        |
|  4 | 23,10,11    |
|  5 | 230,100,110 |
|  6 | 11,100      |
+----+-------------+
6 rows in set

The function

We then create the function (referenced above);

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

The final query

From there, we can do a simple query;

SELECT id from a_table WHERE SPLIT_STR(column2, ",", 1) IN (10,23)

This will give the following result set;

SELECT id from a_table WHERE SPLIT_STR(column2, ",", 1) IN(10,23);
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set

To add more numbers

To add more numbers, simply add to the IN() function - comma separated values.

A note

Deeply consider this

Don't store comma separated values in a single column. The problem you have with the query stems directly from that bad design choice. – a_horse_with_no_name