this is row in option column in table oc_cart
20,228,27,229
why no result found when value is 228 but result found when value is 20 like below :
select 1 from dual
where 228 in (select option as option from oc_cart)
and result found when I change value to 20 like
select 1 from dual
where 20 in (select option as option from oc_cart)
The option column data type is TEXT
In SQL, these two expressions are different:
WHERE 228 in ('20,228,27,229')
WHERE 228 in ('20','228','27','229')
The first example compares the integer 228 to a single string value, whose leading numeric characters can be converted to the integer 20. That's what happens. 228 is compared to 20, and fails.
The second example compares the integer 228 to a list of four values, each can be converted to different integers, and 228 matches the second integer 228.
Your subquery is returning a single string, not a list of values. If your oc_cart.option holds a single string, you can't use the IN( ) predicate in the way you're doing.
A workaround is this:
WHERE FIND_IN_SET(228, (SELECT option FROM oc_cart WHERE...))
But this is awkward. You really should not be storing strings of comma-separated numbers if you want to search for an individual number in the string. See my answer to Is storing a delimited list in a database column really that bad?
