Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get records that look like a string with several mixed words

Database type: MySql

Supposing there are two rows like this in my table:

[1.] "Peter went to the park yesterday"
[2.] "Peter is going the park tomorrow"

...and assuming I have this as pattern string:

"Mark went to the cinema yesterday"

...How could I make a query to get the first record? There could be many other cases where the first record would be the one I am looking for:

-"Leonard went to the Library last night"
-"Jake went to London last year"

...and so on.


Note: I am not showing the table structure because the real issue is the condition I have to look for. However, we could suppose the table is called table, and there are two fields (id and string)

Edit: I know there are FULLTEXT index in MySql which may help to get this work. I have also read they do not work with innoDB tables (which I am working with). (If it is just impossible, I could redesign my DB to make this table be MyISAM)

Edit: As eggyal says in the first comment to this question, "v5.6" supports innoDB tables too.

Edit: Another example would be:

[1.] The heaven looks blue today
[2.] The fire looks red today

And I want to get the first record by assuming:

The ocean looks blue today

...as pattern string.

(In this case, the second record would be a match too, with less relevance than the first one, since it contains looks $ today)

like image 312
Mark Tower Avatar asked Oct 22 '22 21:10

Mark Tower


1 Answers

You can use like in the query. See following examples:

mysql> select * from statements;
+----------------------------------+
| sentence                         |
+----------------------------------+
| Peter went to the park yesterday |
| Mark went to the cinema          |
| Peter is going the park tomorrow |
| Mark went to the park yesterday  |
| Mark went to the park tommorow   |
+----------------------------------+
5 rows in set (0.00 sec)

mysql> select * from statements where sentence like '% went to the park yesterday';
+----------------------------------+
| sentence                         |
+----------------------------------+
| Peter went to the park yesterday |
| Mark went to the park yesterday  |
+----------------------------------+
2 rows in set (0.00 sec)

mysql> select * from statements where sentence like '% went to the park %';
+----------------------------------+
| sentence                         |
+----------------------------------+
| Peter went to the park yesterday |
| Mark went to the park yesterday  |
| Mark went to the park tommorow   |
+----------------------------------+
3 rows in set (0.00 sec)
like image 194
Suku Avatar answered Oct 31 '22 11:10

Suku