MYSQL uses a character function to filter data

This article mainly introduces MYSQL A skillful use of characters function to do data filtering. Friends in need can refer to the following

Problem description:

Structure:

test has two fields,
are col1 and col2, both are character fields, the contents in
are three numbers separated by , and There is a one-to-one correspondence.

For example, the content of col1 is: 26,59,6
The content of col2 is: 1502.5,1690,2276.77
The one-to-one correspondence means that the value of 26 is 1502.5, and the value of 59 is 1690 , 6 corresponds to 2276.77

Search conditions:

Select an id, such as 59, and then enter a number, such as: 2000
Then search for the existence of col1 For records with id=59, then search for col2 less than 2000, that is, 169072d5e9035b37c8e0419b225e5e20b7742000
1520.77< ;2000

drop table test; 
create table test ( col1 varchar(100),col2 varchar(100)); 
insert test select 
'26,59,6', '1502.5,1690,2276.77' union all select 
'59,33,6', '3502.1,1020,2276.77' union all select 
'22,8,59', '1332.6,2900,1520.77'; 
select col1,col2 
from (select *,find_in_set('59',col1) as rn from test) k 
where substring_index(concat(',',substring_index(col2,',',rn)),',',-1) 
 <&#39;2000&#39;;

+---------+---------------------+
| col1    | col2                |
+---------+---------------------+
| 26,59,6 | 1502.5,1690,2276.77 |
| 22,8,59 | 1332.6,2900,1520.77 |
+---------+---------------------+

The above is the detailed content of MYSQL uses a character function to filter data. For more information, please follow other related articles on the PHP Chinese website!