MySQL Where RLIKE any word of sentence

Question

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

This site is for programming questions, not a place to dump "I want, gimme" demands. — Marc B, Aug 09 '16 at 17:42
@MarcB It is a programmign question. However, it is true that you (Mohamed) should show us what you tried. Because this isn't a site where you can just ask for code. — litelite, Aug 09 '16 at 18:04
@litelite IMO you're being too hard on him. It's true that usually people should specify what they tried but sometimes a person doesn't know how to approach a problem... Also consider that this is a very specific question, not a broad request for building a complete program or doing an entire homework exercise... — obe, Aug 09 '16 at 18:32

obe · Accepted Answer · 2016-08-09T19:53:21.443

1

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").

edited Aug 09 '16 at 19:53

answered Aug 09 '16 at 17:50

obe

7,378
5
31
40

For the sake of future readers. Can you explain why your solution works. – litelite Aug 09 '16 at 19:30
@obe Thank you. but this is not true and return only id=1. because "apple iphone" is not like iphone or apple or 5! – Mohammad Javad Khademian Aug 09 '16 at 19:31
@MohammadJavadKhademian Did you run it? The `REPLACE` changes the spaces into pipes which make the regex OR. I tried it with an actual table and it worked... – obe Aug 09 '16 at 19:49
in fact i need something like this: SELECT * FROM tbl WHERE REPLACE(column1, ' ', '|') RLIKE column2 but i don't know syntax – Mohammad Javad Khademian Aug 09 '16 at 19:51
Can you give an example where my query *doesn't* return a result that fits your criteria or returns a result that doesn't? I ran it against your sample table and got rows 1, 2, and 4... – obe Aug 09 '16 at 19:54
@obe it seems works. Thank you. but for me to know more...why "apple iphone" rlike "iphone" or "apple" or "5" ? – Mohammad Javad Khademian Aug 09 '16 at 20:10
Because it's "apple iphone" RLIKE "iphone|apple|5", so it matches if "apple iphone" contains at least one of the "iphone", "apple", and "5" words... "|" is regex OR – obe Aug 09 '16 at 20:38
@obe i found a problem. please help me to solve it. your query fails if data contains 2 space next to each other or additional space at end of data :( – Mohammad Javad Khademian Aug 09 '16 at 23:39
1

@MohammadJavadKhademian There might be a more elegant way, but one thing you could do is pick a character or text that will never appear in your string and use it like this: `... RLIKE REPLACE(column1, ' ', '|(willNeverAppear)?')`. This works because previously, when there were consecutive spaces, the `REPLACE` yielded something like: `apple||iphone`, and the change would make it: `apple|(willNeverAppear)?|(willNeverAppear)?iphone` - which is a valid regex and, assuming that `willNeverAppear` will never appear in your text - also correct. – obe Aug 12 '16 at 00:20

MySQL Where RLIKE any word of sentence

1 Answers1