1

I am having an issue with a full text search that allows the user enter:-

  • multiple search terms
  • multiple partial terms
  • search terms with apostrophes
  • search terms without apostrophes

I have the search working for all but one instance - when the user keys in a partial search term or full search term without the apostrophe.

Within the database there is data saved with Bishop’s Stafford (with apostrophe) and Bishops Stafford (without apostrophe) and I need to fetch all records when the name is searched for.

For place name Bishop’s Stafford, hotel, suite

If the user keys in Bishop’s S, hotel, suite or indeed any version of Bishop’s Stafford containing the apostrophe – the search will work

USE `waf-bit`;
SELECT townDetails FROM town_overview
WHERE MATCH(townDetails) against('+Bishop\'s S* +hotel +suite' IN BOOLEAN MODE) 
OR MATCH(townDetails) against('+Bishops S* +hotel +suite' IN BOOLEAN MODE) 
ORDER BY townDetails ASC LIMIT 50 OFFSET 0

If however the user keys in Bishops S hotel, suite – or indeed any version of Bishops Stafford without the apostrophe – the search will not work, it will not find the version of Bishop’s Stafford with the apostrophe.

USE `waf-bit`;
SELECT townDetails FROM town_overview
WHERE MATCH(townDetails) against('+Bishops S* +hotel +suite' IN BOOLEAN MODE) 
OR MATCH(townDetails) against('+Bishops S* +hotel +suite' IN BOOLEAN MODE) 
ORDER BY townDetails ASC LIMIT 50 OFFSET 0

I do not want to use LIKE as the dataset is too large, I can set up a cross reference table that would indicate which place names contain apostrophes but if I can fix this in the search itself that is the preferred option.

Any help would be greatly appreciated.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
user10342633
  • 11
  • 1

1 Answers1

0

My suggestion is to use UNION operation for these two queries. UNION will also automatically remove duplicates (you should use UNION ALL to see duplicates)

Anton
  • 919
  • 7
  • 22
  • Hi Anton, thanks for the reply. A union will not get around the issue of selecting text with an apostrophe when none is entered in the search string - so when Bishops S is entered once the user has entered any letters after where the apostrophe appears in the word neither fetch will return Bishop's Stafford entries (with the apostrophe) it will return the Bishops Stafford entries however. – user10342633 Sep 20 '18 at 09:30
  • Not a very fast walkaround solution would be the following: if user enters "Bishops" or "Orleans" or anything else on website, use script to truncate such words for 1 or 2 characters like "Bishop" or "Bisho" and then perform database search using "Bishop*" or "Bisho*". – Anton Sep 20 '18 at 09:46