RLIKE with subquery?

Question

I have a query

select X from survey
where survey_date > date_add(curdate(), interval -90 day)

which shows which X are current. But unfortunately through circumstances outside my control, the returned values are not a list of values but a list of lists of comma-separated values. So it returns

| X                  |
+--------------------+
|thing1              |
|thing2,thing3,thing4|
|thing5              |
|thing6,thing7       |

and so forth.

Now I wanted to use this as a subquery. If the data was one to a line, I would do

select Y from othertable
where irrelevant_conditions && Y not in (
    select X from survey
    where survey_date > date_add(curdate(), interval -90 day)
);

But this does not work since I might have, say thing3.

Instead I need to do RLIKE. (As it happens the data cannot collide by their form, so no extra regexp magic is needed here to check for commas.) Is that possible in MySQL?

score 2 · Accepted Answer · edited May 23 '17 at 10:08

2

To search into a comma separated string you can use find_in_set. If Y is not an string a implicid cast operation is maded.

select Y from othertable 
where irrelevant_conditions && not exists (
    select  1
    from survey
    where 
     survey_date > date_add(curdate(), interval -90 day) and
     find_in_set(othertable.Y, X) > 0 
);

edited May 23 '17 at 10:08

Community

1
1

answered May 03 '12 at 19:04

dani herrera

48,760
8
117
177

That looks like just the right thing. But when I run the subquery with some specific value for Y it gives me nothing when it should give me something. – Charles May 03 '12 at 19:36
the parameters are reversed! try `select find_in_set('bar', f) ...` – Bohemian May 03 '12 at 19:44

score 0 · Answer 2 · answered May 03 '12 at 19:47

0

danhip's convoluted query can be simplified to:

select Y from othertable 
join survey on survey_date > date_add(curdate(), interval -90 day)
    and find_in_set(othertable.Y, X) = 0 
where irrelevant_conditions

answered May 03 '12 at 19:47

Bohemian

412,405
93
575
722

This pulls up cases where there is some row for which `find_in_set(othertable.Y, X) = 0`, rather than for which it is 0 for all rows. – Charles May 03 '12 at 20:17

RLIKE with subquery?

2 Answers2