MySQL uses string as the query Condition

MySQL uses string as the query Condition

Today, I encountered a problem when querying MySQL. The scenario is like this. Assume that one of my tables has a field scode (indicating the product number ), then there is a string that stores multiple product numbers. For example, "0001ME, 0002BL" is a string. If it is str, it is intended to use in, that is to say, we want to use str as the condition of in, as shown in the following where scode in "0001ME, 0002BL", but it fails. Then we can refer to the MySQL documentation and find various functions, the instr function is found and the problem is solved. The result process is described below.

1. At the beginning, str is being converted to a similar in parameter, as shown below:

SELECT * FROM stocks_orderlist s where s. scode in ("0001ME", "0002BL") LIMIT 10;

Select convert (CONCAT ("\ '", REPLACE ("0001ME, 0002BL ","\,","\'\,\'"),"\'"), CHAR );

SELECT * FROM stocks_orderlist s where s. scode in (CONVERT (CONCAT ("\" ", REPLACE (" 0001ME, 0002BL ","\,","\"\,\""), "\" "), CHAR) LIMIT 10;

Although str is converted into what in requires, but later I thought that the in parameter is a list set, and I converted str into the so-called in parameter form in time, but it must be a string, so I had been busy for a long time and wanted to give up a bit. But when I thought about it, I certainly did not find a proper solution, so I found the instr function.

2. instr function. See the help document to find out the first problem that substr encountered in str. Try the following:

Select instr ("0001ME, 0002BL", "0003ME ");
Select instr ("0001ME, 0002BL", "0001ME ");
Select instr ("0001ME, 0002BL", "0002BL ");
SELECT * FROM stocks_orderlist s where INSTR ("0001ME, 0002BL", s. scode );

Summary: A very small method seems to be very painful if you do not know the method.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: