如何将逗号分隔的字符串与另一个逗号分隔的字符串匹配

i want to match 2 comma separated string in mysql,

我要在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

这是我的查询:http://sqlfiddle.com/ ! 9 / b1931

2 个解决方案

#1

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

使用regexp(需要用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

或者不用PHP reformat: http://sqlfiddle.com/#!9/b1931/40

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

#2

You can use REGEXP with two line of code:-

您可以使用REGEXP两行代码:-。

<?php

$test_string = 'Housekeeping,Cleaning,Other';

$test_string = implode('|',explode(',',$test_string));// now use this variable inside  query

Output:- https://eval.in/895411

输出:https://eval.in/895411

Output I got with this query:-http://sqlfiddle.com/#!9/b1931/15

我通过这个查询得到的输出:-http://sqlfiddle.com/#!9/b1931/15

Important Note:-

重要提示:

Never store data as comma separated items. normalise your data as much as possible.

不要将数据存储为逗号分隔的项。尽可能将数据标准化。

Reference:-

参考:

MySQL :Regular Expressions

MySQL:正则表达式

#1