CODEWITHSUNDEEP
mysql
Harsh Agarwal
Harsh Agarwal

Reputation: 795

use full text search to search incomplete words in mysql

I am making a library management system. I have a problem in the search for a book from mysql database.

For searching data in mysql we use full text search .

But it only works if a full word is given. If user enters an incomplete word instead of the actual word , is there any function to search.

ex : if book name is calculus, if user types calc , then also the books should come

Upvotes: 2

Views: 1444

Answers (2)

Shadow
Shadow

Reputation: 34231

You can try using fulltext search with boolean mode, which allows a few extra operators. You will be interested in the truncation operator (*):

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

If a word is specified with the truncation operator, it is not stripped from a boolean query, even if it is too short or a stopword. Whether a word is too short is determined from the innodb_ft_min_token_size setting for InnoDB tables, or ft_min_word_len for MyISAM tables. These options are not applicable to FULLTEXT indexes that use the ngram parser.

The wildcarded word is considered as a prefix that must be present at the start of one or more words. If the minimum word length is 4, a search for '+word +the*' could return fewer rows than a search for '+word +the', because the second query ignores the too-short search term the.

Pls note, that you cannot start an expression with the * operator, so the results cannot include a book, which title contains 'calc', only which title starts with 'calc'.

Upvotes: 1

F4bio16
F4bio16

Reputation: 79

You can use the LIKE operator with the "%" wildcard

With LIKE you can use the following two wildcard characters in the pattern:

% matches any number of characters, even zero characters.

for example

SELECT * FROM <Table> where book like "%calc%";

http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

Upvotes: 0

Related Questions