CODEWITHSUNDEEP
phpmysqlsqldatabase
arjun_web
arjun_web

Reputation: 131

how do i match comma separated string with another comma separated string

i want to match 2 comma separated string in mysql,

test string => Housekeeping,Cleaning,Other.

|              skills                 |
+-------------------------------------+
|    Housekeeping,Cleaning,Sweeping   |
+-------------------------------------+
|  Housewives,Beautician,Cleaning     |        AGAINST  `Housekeeping,Cleaning,Other`
+-------------------------------------+
|        PHP,Laravel,Other            |
+-------------------------------------+
|   Housekeeping,housekeeping,other   |
+-------------------------------------+


MUST MATCH  =>  All the `rows`

i heard about LOCATE syntax but don't know to use.

SELECT * FROM jobs_posted_by_employer WHERE skills [don't know]

i have kept my table online for query execution!!!

here is my Query:http://sqlfiddle.com/#!9/b1931

Upvotes: 3

Views: 89

Answers (1)

Indent
Indent

Reputation: 4967

Using regexp (you need reformat a little your input with PHP) : http://sqlfiddle.com/#!9/b1931/13

select
    *
from
    jobs_posted_by_employer
where
    skills regexp '(^|,)Housekeeping|Cleaning|Other(,|$)'

or without PHP reformat : http://sqlfiddle.com/#!9/b1931/40

select
    *
from
    jobs_posted_by_employer
where
    skills  regexp concat(
        '(^|,)',
        replace('Housekeeping,Cleaning,Other',',','|'),
        '(,|$)'
    )

Upvotes: 2

Related Questions