mysqlregexrlike

MySQL Where RLIKE any word of sentence

I have a table like this:

+-------------+-------------------+----------------+
|id           | column1           | column2        |
+-------------+-------------------+----------------+
| 1           | apple iphone 6s   | iphone         |
| 2           | iphone apple 5    | apple iphone   |
| 3           | iphone 4          | samsung        |
| 4           | iphone 4          | apple iphone 6 |
+-------------+-------------------+----------------+

How can I return all the records where, any word of column1 is included in column2 using rlike statement? (in this example id=1,2,4)

Thank you

Solution

  • Try this:

    SELECT * FROM tbl WHERE column2 RLIKE REPLACE(column1, ' ', '|')

    The REPLACE replaces all occurrences of ' ' with '|', which essentially creates a regex that matches strings that contain any of the space-separated words in column1 (e.g. "apple|iphone|6s").