where clause - mysql search for multiple substring in single column -

where clause - mysql search for multiple substring in single column -

i need efficient way search multiple sub string in same column of table. next sample table , inefficient query create. can 1 help me create 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 * `table1` `column_2` '%10%' , `column_2` '%23%';

these values lengthy , and status dynamic in amount. there efficient way create query remove , status (having).

i took function split_str here: mysql - array info type, split string,

our data-set

assume have next info set;

select * 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 create function (referenced above);

create function split_str( x varchar(255), delim varchar(12), pos int ) returns varchar(255) homecoming replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos -1)) + 1), delim, ''); the final query

from there, can simple query;

select id a_table split_str(column2, ",", 1) in (10,23)

this give next result set;

select id a_table split_str(column2, ",", 1) in(10,23); +----+ | id | +----+ | 1 | | 4 | +----+ 2 rows in set to add together more numbers

to add together more numbers, add together in() function - comma separated values.

a note

deeply consider this

don't store comma separated values in single column. problem have query stems straight bad design choice. – a_horse_with_no_name

mysql where-clause