dreamluverz collection
Let’s dream together

MYSQL: 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 I used 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 .

SOURCE: http://bugs.mysql.com/bug.php?id=2047

I use FIND_IN_SET() to 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);

More »

Tags: bug, find_in_set, in(), mysql

IN()

At first I thought this is not possible So I just gave it a try and it works

update TABLE set field=’s’ WHERE id IN (’1′, ‘2′)

Tags: in(), mysql, sql