FIND_IN_SET AND IN()

Using IN() for comma delimited and my query is something like SELECT * FROM TABLE WHERE FLD IN(’33, 148′)
TABLE

(ID) (FLD) COMMA DELIMITED FIELD
1 42, 148
2 234, 25423,148

It wont be able to retrieve any records at all coz it will only check for the exact values inside that quote ’33, 148′. Even I put something like SELECT * FROM TABLE WHERE FLD IN(’148′) still won’t have any result coz I don’t have a record that’s only ’148′

So instead use something like SELECT * FROM TABLE WHERE FIND_IN_SET( ’148′, FLD) and these 2 rows will be retrieved. So I guess IN() is not a good way to retrieve fields with comma delimited values unless it’s the exact string. But if ur sql is like this SELECT * FROM TABLE WHERE ID IN(1,2), it won’t be a prob at all.

Btw. I tried the issue below and it’s not a prob anymore. I only used the sql like SELECT * FROM TABLE WHERE FIND_IN_SET( ’1487′, FLD) and it only retrieved 1487 .

FIND_IN_SET() return results containing a certain number in a comma-delimmeted
list in a TEXT field. Sometimes it returns false positives,
for example:mysql> SELECT pssmID_list FROM bccs WHERE FIND_IN_SET(1487, pssmID_list);


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


| pssmID_list |


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


| 7771,148 |


| 1487 |


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


2 rows in set (0.01 sec)


How to repeat:


This apparently happens when the last element of the list is a partial match for the query
string. It is difficult to repeat, since it only happens in some of these cases. If you
use static strings in the query, rather than querying the table, it won't happen. I
suspect it's caused by FIND_IN_SET going past the end of a text string and into the next
record.
To repeat, I recomend writing a script to check for excess returns. Create a large table
of lists of numbers, in which each number should appear in only one row. Then cound the
responses to find_in_set for each number. If you come back with more than 1, the error
has occured.


The lack of repeatability is the reason I marked this serious. For me, it caused problems
in 11 searches out 6000. But in some applications, this could be serious and hard to
notice.

Suggested fix:

As a workaround, you can append a comma to the delimited list:

mysql> SELECT pssmID_list FROM bccs WHERE FIND_IN_SET('1487', CONCAT(pssmID_list, ','));


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


| pssmID_list |


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


| 1487 |


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


1 row in set (0.00 sec)