having trouble with FullTextSearch

Question

I'm new with MySQL FULLTEXTSEARCH. Here's what I have tried,

SELECT * 
FROM example_myisam
WHERE MATCH(data, data2) AGAINST ('+*ar' IN BOOLEAN MODE);

but it gave me zero result. What I'm looking is to convert this LIKE statement in FULLTEXT

SELECT *
FROM example_myisam 
WHERE data LIKE '%ar' OR
      data2 LIKE '%ar';

here's a demo if it helps: http://www.sqlfiddle.com/#!2/d3fdd/12

Here's also my DDL statement:

CREATE TABLE example_myisam
(
    id     INT,
    data   VARCHAR(50),
    data2  VARCHAR(50)
) ENGINE = MYISAM;

ALTER TABLE example_myisam ADD FULLTEXT alltext (data, data2);

Do you have FULLTEXT indexes set up in your table? – Unexpected Pair of Colons Apr 25 '13 at 16:12 — Unexpected Pair of Colons, Apr 25 '13 at 16:12

score 1 · Answer 1 · answered Apr 25 '13 at 16:27

1

You may have to do a combination of MATCH()...AGAINST() and LIKE to get the results you want. The * is used to find words with a prefix. So, it has to be used like Vict*.

answered Apr 25 '13 at 16:27

Unexpected Pair of Colons

770
1
7
19

is there no other way to search for containing string? – Skinny Pipes Apr 25 '13 at 16:31
I honestly think this is your best option. Unless you want to pull all of the results, and sort through them with a regex in your code, which I don't think you want to do. – Unexpected Pair of Colons Apr 25 '13 at 16:35

having trouble with FullTextSearch

1 Answers1