0

I'm actually using a view that is adding a column including a boolean value based on some regex criteria:

select if(((tableA.text regexp 'textA|textB|blablabla') = 1),'YES','NO') AS`BLACK_LIST, tableA.text, tableA.ID
FROM tableA

I need to make all the regexp terms dynamic and to be included in a separate table, I'm trying something like:

 select if(((tableA.text regexp IN (select words from tableB)) = 1),'YES','NO') AS`BLACK_LIST, tableA.text, tableA.ID
    FROM tableA

But it does not work. Thx

SimonFreeman
  • 205
  • 1
  • 7
  • 17
  • Provide tables structures and example data on sqlfiddle.com.. Also add in expected results. – Raymond Nijland Apr 27 '18 at 14:13
  • 1
    Short answer... you need to go dynamic sql with this one.. `PREPARE` ( https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html ) is a way to go.. And use `GROUP_CONCAT(column ORDER BY column SEPARATOR "|")` ( https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat ) to generate the dynamic regex..Or use MySQL's user variables.. `SET @regex = (SELECT GROUP_CONCAT(...) ...;`` So you can use `select if(((tableA.text regexp @regex...` – Raymond Nijland Apr 27 '18 at 14:22
  • thank you! with SET @regex = (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM tableB it works perfectly! – SimonFreeman Apr 27 '18 at 14:51

0 Answers0