Using wildcards in MySQL fulltext searches

I'm using a MySQL database, and I have a table containing a text field. I was using a query to return all rows containing a string within the text field -

... WHERE desc like '%MSDN%'

But this is was too slow on a large table. I speeded this up by using a fulltext index on the 'desc' column in my table.
However, the equivalent fulltext search clause


returns rows matching

...w/MSDN Prem...

but not


I've tried modifying my fulltext search with wildcards i.e.

... MATCH desc AGAINST ('*MSDN*')

but this didn't work, I still didn't get the complete set of matches I expected. Does anyone know what the equivalent fulltext search pattern is, to return exactly the same resultset as my original query?
Who is Participating?
jericotolentinoConnect With a Mentor Commented:
Try if MySQL boolean search works for you:


It requires that you're using MyISAM and the indexes you're searching are of type varchar or text.

More info is available here:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
... MATCH desc AGAINST ('MSDN*')

Open in new window

smartstream-stpAuthor Commented:
Unfortunately that doesn't work. 'MSDN*' does not return rows containing '...w/MSDNPrem...'
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
first you cannot use *something in FT...

so, I am not sure, but maybe / is not a word breaker....

#If you want to change the set of characters that are considered word characters, you can do so in two ways. Suppose that you want to treat the hyphen character ('-') as a word character. Use either of these methods:
    *    Modify the MySQL source: In myisam/ftdefs.h, see the true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.
    *    Modify a character set file: This requires no recompilation. The true_word_char() macro uses a character type table to distinguish letters and numbers from other characters. . You can edit the <ctype><map> contents in one of the character set XML files to specify that '-' is a letter. Then use the given character set for your FULLTEXT indexes.
After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes.

Open in new window

smartstream-stpAuthor Commented:
If '/' isn't a word breaker, then how is 'w/MSDN Prem' matched, but not 'w/MSDNPrem' ?

I don't think fulltext indexing is going to be 100% reliable for me, as it relies on the text field being split into words. In my case I can't guarantee this. I don't think there is any direct equivalent for 'WHERE desc LIKE '%MSDN%' i.e. matching an aribitrary string within a text field.

smartstream-stpAuthor Commented:
jericotolentino's suggestion worked much better, but didn't match a field value of

VStudio Team Ed Sft Test AllLngLic/SAOLVNL1YRAcqY1wMSDNPrmAdPrdPrtnrOnly
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.