• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

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

0
crafuse
Asked:
crafuse
1 Solution
 
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
 
johanntagleCommented:
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
 
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now