I select from database but i want add comma to value before select
Table:
id value
1 5,6
2 7,5,9,8
3 4,7,6
I want select value if LIKE ,6, return ID 1 and 3
My code not work :
SELECT * FROM `,value,` LIKE ',6,'
Asked
Active
Viewed 181 times
-1
Mohammad
- 111
- 2
- 13
-
What is the problem to use `SELECT * FROM table_name WHERE value LIKE '%6%';`? – D.Dimitrioglo Aug 28 '16 at 08:06
-
Even `6` is not in the value list you will get output for values like `66 or 16 or ...etc` @D.Dimitrioglo – 1000111 Aug 28 '16 at 08:07
-
'%,6%' OR '%6,%'? – D.Dimitrioglo Aug 28 '16 at 08:23
-
what if `6` is the only element in the `value` column? @D.Dimitrioglo. One way you can make use of `like` in this case : `....CONCAT('%,',6,',%') LIKE CONCAT(',',`value`,',')....` – 1000111 Aug 28 '16 at 10:06
1 Answers
1
Try with find_in_set
SELECT * FROM your_table WHERE FIND_IN_SET(6,`value`);
OR
SELECT * FROM your_table WHERE CONCAT('%,',6,',%') LIKE CONCAT(',',`value`,',').
Note on FIND_IN_SET:
FIND_IN_SET() function
MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings. The string list itself is a string contains substrings separated by ‘,’ (comma) character.
This function returns 0 when search string does not exist in the string list and returns NULL if either of the arguments is NULL.
Syntax :
FIND_IN_SET (search string, string list)
Arguments
Name Description
search string A string which is to be looked for in following list of arguments.
string list List of strings to be searched if they contain the search string.
Caution:
A must read:
Is storing a delimited list in a database column really that bad?
Yes
