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)
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With