(Mysql) how to determine whether a numeric variable is included in a table field and list all data in the table containing the variable. if the table contains the following idcsite fields .... 11,2, 13 .... & nbsp; 22,3, 4 ...., 5 ...., 23 .... 5. how does mysql determine whether a numeric variable is included in a table field and list all the data in the table containing the variable.
If the table contains the following fields:
Id csite ....
1 1, 2, 13 ....
2, 3, 4 ....
3, 5 ....
4, 5, 23 ....
5 1, 3, 8 ....
Suppose we get a number variable 3 and how to list all data rows containing the number 3
Use select id, csite from table where csite like '% 100' or csite like' % 3, % 'or csite like' %, 3, % 'or csite like' %, 3%'
This will not work, and we will also find out 13, 23
The expected result is:
Id csite ....
2, 3, 4 ....
5 1, 3, 8 ....
I checked some information and tried using this charindex (3, csite)> 0. what should I do ??
Not too complex SQL statements and stored procedures
Thank you !!!!!
------ Solution --------------------
You can use the MySQL function FIND_IN_SET.
FIND_IN_SET (str, strlist)
Returns a value if the str string is in the strlist consisting of N substrings, a value ranging from 1 to N is returned. A string list consists of multiple substrings separated by the character. If the first parameter is a constant string and the second parameter is of the SET column type, the FIND_IN_SET () function will be optimized to use bitwise operations! If str is not in strlist or if strlist is an empty string, the return value is 0. If any parameter is NULL, the return value is also NULL. If the first parameter contains a ",", this function will not work at all:
Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D ');
-> 2
