[MySQL] Search literal within a word

Question

Is there a way to perform a FULLTEXT search which returns literals found within words?

I have been using MATCH(col) AGAINST('+literal*' IN BOOLEAN MODE) but it fails if the text is like:

• blah,blah,literal,blah
• blahliteralblah
• blah,blah,literal

Please Note that there is no space after commas.

I want all three cases above to be returned.

Answer 1

Hello,

I think that should be better fetching the array of entries and then perform a text manipulation over the fetched data (in this case a search)!

Because any text manipulation or complex query take more resources and if your database contains a lot of data, the query become too slow! Moreover, if you are running your query on a shared server, that increases the performance issues!

You can easily accomplish what you are trying to do with regex, once you have fetched the data from the database!

---

UPDATE: My suggestion is the same even if you are running your script on a dedicated server! However, if you want to perform a full-text search of the word "literal" in BOOLEAN MODE like you have described, you can remove the + operator (because you are searching only one word) and construct the query as follow:

SELECT listOfColumsNames WHERE
MATCH (colName) 
AGAINST ('literal*' IN BOOLEAN MODE);

However, even if you add the AND operator, your query works fine: tested on Apache Server with MySQL 5.1!

I suggest you to read the documentation about the full-text search in boolean mode.

The only one problem of this query is that doesn't matches the word "literal" if it is a sub-string inside an other word, for example: "textliteraltext". As you noticed, you can't use the * operator at the beginning of the word!

So, to accomplish what you are trying to do, the fastest and easiest way is to follow the suggestion of Paul, using the % placeholder:

SELECT listOfColumsNames 
WHERE colName LIKE '%literal%';