Compare a value from a CSV column in mysql with more checks

Question

I need to have a query which has two parts. I mean two where clause.

till now I have reached to the following, but it dosen't work properly.

SELECT * from items
where
      FIND_IN_SET( '3', category_id )
  AND postcode LIKE 'sw19%'
order by id

this query only runs the first part, i.e., the FIND_IN_SET() part. and just ignores the second part i.e., Postcode check...

I want to know is there a solution for this kind of query.

DETAILS

I want to compare the values from 2 columns:

• 1 column is a csv column , eg. 1,2,3,4,5

• the other is postcode B17 SW19 etc

Table is like the following.

---+------+-------------+----------+-
id | item | category_id | postcode |
---+------+-------------+----------+-
 1 | abc  | 1,2,3,4     | SW19     |
---+------+-------------+----------+-
 2 | def  | 3,4,5       | NW6      |
---+------+-------------+----------+-
 3 | xyz  | 6,7,8,9     | SW19     |
---+------+-------------+----------+-
 4 | ghi  | 8,9,10,11   | SW19     |
---+------+-------------+----------+-

etc.

if I want to select an entry whose category_id contains '3' AND whose postcode starts from 'SW', what query will it be?

Answer 1

I just found out the solution which completely resolves my problem and though it would help someone in future.

Well, the query goes like this:

Select * from table_name where FIND_IN_SET('3', category_id) >0 AND postcode LIKE 'sw%'

Regards, Shoaib.