MYSQL's clever use of character functions for data filtering

This article mainly introduces a question about using character functions to filter data in MYSQL. Friends who need it can refer to it. I hope it can help everyone.

Problem description:

Structure:

test has two fields,
are col1 and col2, both are character fields,
inside The contents are three numbers separated by ",", and correspond one to one.

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, 59 is 1690, and 6 corresponds to 2276.77

Search conditions:

Select an id, such as 59, and enter a number, such as :2000
Then search for the record with id=59 in col1, and then search for col2 less than 2000, that is, 1690<2000

For example:

If there are the following three records, the search id is 59, records with values ​​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

The search found that the ID of these three records is 59, and then judged that the second search condition should be (that is, using the number corresponding to the ID position to compare):

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 |

+---------+---------------------+

Related recommendations:

php replaces some characters in a string function str_ireplace()

php returns a character function chr() from a specified ASCII value

Detailed explanation of character functions in MySQL database

The above is the detailed content of MYSQL's clever use of character functions for data filtering. For more information, please follow other related articles on the PHP Chinese website!