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, 1690<2000

Example:

If there are the following three records, search for records with id 59 and a value less than 2000:

26,59,6 | 1502.5,1690,2276.77
59,33,6 | 3502.1,1020,2276.77
22,8,59 | 1332.6,2900,1520.77

Search These three records have an ID of 59, and then it is determined that the second search condition should be (that is, compared with the number corresponding to the ID position):

1690<2000
3502.1>2000
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!