1

I am trying to use a where clause with like on a column. However I need it to use a subquery to search against multiple phrases.

This question is similar to MySQL LIKE IN()?

Right now my query looks like this.

WHERE hashtag REGEXP 'indiana|iu|bloomington'

However I need to do a subquery where the string is so my query would be something like this

WHERE hashtag REGEXP (select name from hashtags where hid = 1)

The above line obviously doesn't work and I'm not sure if REGEXP would be best in this situation. Is there a way to use LIKE or REGEXP against multiple strings using a subquery? Perhaps there is a better way to do this without a subquery that I just don't see at the moment. Any help or direction is appreciated, thank you.

Community
  • 1
  • 1
Caimen
  • 2,623
  • 2
  • 26
  • 43

2 Answers2

2

You can do this:

WHERE hashtag REGEXP (select GROUP_CONCAT(name SEPARATOR '|') from hashtags where hid = 1)
Doug Kress
  • 3,537
  • 1
  • 13
  • 19
1

You can also JOIN on a REGEXP:

SELECT mt.hashtag, ht.name
  FROM mytable mt
  JOIN hashtags ht
    ON mt.hashtag REGEXP ht.name
 WHERE ht.hid = 1;
Mike
  • 21,301
  • 2
  • 42
  • 65