Search for substrings in MySQL fulltext index

Experts -

created a fulltext index on field name in table users. am i not able to search for substrings in a mysql fulltext index? for example, how would i translate this query:

select * from users where name like '%ana%';

which would then return all users with the letters 'ana' in their name.

this is done in MS SQL Server using the fulltext predicate 'contains'. is there anything similar in MySQL? or does MySQL fulltext index only return whole words?

thanks,

crafuse

crafuseAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

excerpts:

*  The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

SELECT * FROM TABLE_NAME WHERE MATCH (ft_indexed_column) AGAINST ('apple*' IN BOOLEAN MODE)

will find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.
0
 
OnALearningCurveCommented:
Hi Crafuse,

I think you can use INSTR(str,substr).

This will return the first position of substr within str and returns 0 if it is not found

So in your case you would have a WHERE clause of

WHERE (name, 'ana') <> 0

Hope this helps,

Mark.
0
 
mcs0506Commented:
0
 
crafuseAuthor Commented:
thanks. i discovered this syntax earlier. it 'sort of' works for me, but not really. i have to say, mysql's fulltext indexing is not as flexable as some others. oh well...
0
 
johanntagleCommented:
Yup. If you need powerful full text searching on data in MySQL, consider sphinx - http://sphinxsearch.com/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.