在Mysql中比较逗号分隔的字符串

I need to compare a string with comma separated values in a stored procedure.

我需要在存储过程中将字符串与逗号分隔值进行比较。

input RANDOM string is = "'aa','ab','ac'"

输入RANDOM字符串=“'aa'，'ab'，'ac'”

I need something like

我需要类似的东西

I need a resultant effect similar to the one below without using DYNAMIC SQL.

我需要一个类似于下面的结果效果而不使用DYNAMIC SQL。

SELECT * FROM table1 
WHERE table1.field1 LIKE %aa% OR 
table1.field1 LIKE %ab% OR 
table1.field1 LIKE %ac%

I cannot use find_in_set as the input string is variable and not fixed

我不能使用find_in_set，因为输入字符串是可变的而不是固定的

1 个解决方案

#1

You can approach in this way:

你可以这样接近：

SELECT
    *
FROM table1
WHERE
    table1.field1 REGEXP 
    REPLACE (REPLACE ("'aa','ab','ac'",'\'',''),',','|');

In general:

一般来说：

SELECT
    *
FROM table1
WHERE
    table1.field1 REGEXP 
    REPLACE (REPLACE (PUT_YOUR_INPUT_STRING_HERE,'\'',''),',','|')

In order to understand what's going on there the following query might lead towards that:

为了理解正在发生的事情，以下查询可能会导致：

SELECT 
REPLACE("'aa','ab','ac'",'\'','') afterFirstReplace,
REPLACE(REPLACE("'aa','ab','ac'",'\'',''),',','|') afterSecondReplace;

---

afterFirstReplace               afterSecondReplace
aa,ab,ac                            aa|ab|ac