1

I'd like the user to be able to search for values containing numbers using the numeral, written or roman numeral form. I've found a guide on converting roman numerals to numerals and vice-versa, but this could potentially lead to problems elsewhere in the search as certain records on the database use different forms.

For example, I'd like 'Star Wars: Episode 5', 'Star Wars: Episode five' and 'Star Wars: Episode V' all to be searchable and result in the database entry for 'Star Wars: Episode 5' being returned. I don't want the search to simply swap out the word/numeral/number and only conduct a single search as this would mean that a database entry like 'V for Vendetta' would be unobtainable.

Ideally, the method would be compatible with the 'like' function as that is what my search currently uses.

thomas1988
  • 47
  • 5
  • 1
    One solution involves a full text search with a synonym list. And that is highly dependent on the database you are using. – Gordon Linoff May 16 '20 at 13:03
  • I’ve been reading up on it and it seems that might be the best option, but the sql server I’m using doesn’t have that functionality. I’m considering just adding an ‘alternative name’ column to my table although as this would lead to thousands of empty rows for the sake of maybe 10 rows I might just leave it as it is (the auto fill in my search bar has usually pulled out the correct results by the time the user gets to the number value). I don’t suppose you know how to ignore punctuation though? That’s potentially a larger issue as users rarely type colons and such while searching. – thomas1988 May 16 '20 at 13:05
  • . . Add a new table with alternate names. But then you have to construct queries to search for either the actual name or the alternate name . . . Or, have the new table be "searchable names", which is loaded with all the original titles, plus additional titles you might want to add. By the way, "V" and "5" will disappear anyway due to stop words, word definitions, and lengths, unless you parameterize the full text index correctly. – Gordon Linoff May 16 '20 at 13:35
  • I think alt names would be a fairly simple fix (My current query is ‘... where name like ‘%”$usersearch”%’’ ‘ , so I think I could just tack on ‘or altname like ‘%”$usersearch”%’’ ‘), but whether it’s worth the slowdown in delivering results remains to be seen. As far as I’m aware, there are no stop words in the search at the moment. Thanks anyway. Appreciate you taking the time! – thomas1988 May 16 '20 at 13:52

0 Answers0