Filter MySQL comma separated field

In my table there is field called agency_ids. It will have comma separated string values like below. a0001, a0002. One or may agent ids can contain per record.

Now i need to search the table using given agency id.

Ex - if i give a1235 it should return both rows showed above. If i give a1234 it should return only row with a1234.

How can i do it ? I tried agency_ids IN ('a1234') and FIND_IN_SET but it didn't work.

Complete query -

SELECT ov.*,c.name as company_name 
FROM (SELECT v.vacancy_id,v.company_id,v.designation,v.job_ref_number 
      FROM `t2o_vacancies` AS v  
      WHERE `opening_date` <= '2014-01-27' 
      AND `closing_date` >= '2014-01-27'
      AND posting_type= 'Agency' 
      AND agency_ids IN ('a1234') 
      ORDER BY v.opening_date DESC ) AS ov 
      LEFT JOIN t2o_companies AS c ON ov.company_id = c.id

Answers


Try using like

and agency_ids like '%a1235%'

instead of using IN

AND agency_ids IN ('a1234')

in will allow you to specify multiple values, but it will not look at a1234,a1235 as two different values.


How about using LIKE operator instead of IN?

SELECT ov.*,c.name as company_name 
FROM (SELECT v.vacancy_id,v.company_id,v.designation,v.job_ref_number 
  FROM `t2o_vacancies` AS v  
  WHERE `opening_date` <= '2014-01-27' 
  AND `closing_date` >= '2014-01-27'
  AND posting_type= 'Agency' 
  AND agency_ids LIKE '%a1234%' 
  ORDER BY v.opening_date DESC ) AS ov 
  LEFT JOIN t2o_companies AS c ON ov.company_id = c.id

you can sure use where agency_ids like %a1235% yet this might not be the best approach.

have you considered using relational database ?

if you can add another table to your schema you can do

table t2o_companies
normal structure......

table t2o_vacancies
normal structure......


table t2o_companies_t2o_vacancies
id company_id vacancie_id

i'm not sure whats your current schema is, yet if you gona need to fetch row using agency_id then i would have a join table rather than just saving them in 1 field with comma separation.

yet if you dont want to change schema, then just dont forget to index agency_ids for faster searching.


Need Your Help

Javascript \x escaping

javascript string escaping ascii processing

I've seen a few other programs that have something like this:

Why doesn't Google like my PHP script?

php http

I am trying to connect to http://google.com/ and receive their home page with the following code: