Search code examples
sqlsql-serverfull-text-indexingthesaurus

SQL Server : searching strings for equivalent phrasing such as inch, inches,'' and "

As per the title I am looking for a method to search data on an equivalence basis

Ie user searches for a value of 20" it will also search for 20 inch, 20 inches etc...

I've looked at possibly using full text search and a thesaurus but would have to build my own equivalence library

Is there any other alternatives I should be looking at? Or are there common symbol/word equivalence libraries already written?

EDIT:

I dont mean the like keyword and wild cards

if my data is
A pipe that is 20" wide
A pipe that is 20'' wide - NOTE::(this is 2 single quotes)
A pipe that is 20 cm wide
A pipe that is 20 inch wide
A pipe that is 20 inches wide

I would like to search for '20 inch' and be returned
A pipe that is 20" wide
A pipe that is 20'' wide
A pipe that is 20 inch wide
A pipe that is 20 inches wide

Solution

  • just answering this in case anyone else comes across it as I finally figured it out.

    I ended up using an FTS thesaurus to assign equivalence to inch inches and ", and this work wonderfully for inch and inches but would return no results when I searched for 6"

    It eventually turned out the underlying issue I had was that characters such as " are treated as word breakers by full text search.

    I found that custom dictionary items seems to override the languages word breakers and so introducing a file called Custom0009.lex with a few lines of " and a few other characters/terms I wanted included that had word breakers in to C:\Program Files\Microsoft SQL Server\{instance name}\MSSQL\Binn and restarting the fdhost and rebuilding the index allowed my search for

    select * from tbldescriptions where FREETEXT(MainDesc,'"')

    or

    select * from tbldescriptions where contains(MainDesc,'FORMSOF(Thesaurus,"""")')

    notice the double " on the contains one as the search term is within " already it needed to be escaped to be seen.