Mysql convert FullText Search Query to REGEX

Asked Jul 14 '19 at 07:26

Active Jul 24 '19 at 20:49

Viewed 46 times

I'm working to implement functionality to search records from database in which provided keywords are exists. So I chose FUll Text indexing. But it feels there are some of the drawbacks of this query. like we can't find two characters word. So I'm trying to use REGEX in mysql.

Here is my SQL query with FULLTEXT Query.

SELECT * FROM `table_name` WHERE MATCH (field_name) AGAINST ('+what +that' IN boolean MODE)

And I tried to convert this query to REGEX

SELECT * FROM `table_name` WHERE REGEXP '[[:<:]]what that[[:>:]]'

But this query is not working. Can anyone suggest me what I'm doing wrong in this query?

edited Jul 14 '19 at 07:43

asked Jul 14 '19 at 07:26

Lakhwinder Singh

5,536
5
27
52

in your first example you have `"`in match whereas in your regex you don't have `"` – Code Maniac Jul 14 '19 at 07:41
@CodeManiac Please check my updated question – Lakhwinder Singh Jul 14 '19 at 07:44
can you create a [`snippet here`](http://sqlfiddle.com/) of your question – Code Maniac Jul 14 '19 at 07:48
What is your MySQL version? – Wiktor Stribiżew Jul 14 '19 at 07:51
@CodeManiac here is the snippet `http://sqlfiddle.com/#!9/418cc3/5` – Lakhwinder Singh Jul 14 '19 at 07:57
@WiktorStribiżew Mysql version is 5.6+ – Lakhwinder Singh Jul 14 '19 at 07:58
@LakhwinderSingh something like [`this`](http://sqlfiddle.com/#!9/418cc3/24) can be done, or `[[:<:]]earth .* flat[[:>:]]` like this – Code Maniac Jul 14 '19 at 08:09
The equivalent of that FT query is (approximately) `WHERE txt REGEXP '[[:<:]]what[[:>:]]' AND WHERE txt REGEXP '[[:<:]]that[[:>:]]'` – Rick James Jul 15 '19 at 04:54

Mysql convert FullText Search Query to REGEX

0 Answers0