7

I have a text column containing various words that have been shortened into abbreviations. For example, instead of "insured", the column might contain "insd". I understand I can use a thesaurus file to create a list of synonyms, effectively allowing a search for "insd" to return rows that contain both "insured" and "insd". Perfect.

However, some of the other abbreviations in the column contain "special" characters, like a slash or an ampersand, such as:

t/p - third party
o/s - other side
p/p - per person
i&o - inside and out

Is there some way I can make CONTAINS or FREETEXT understand queries containing these words? For instance:

SELECT *
FROM dbo.MyTable
WHERE FREETEXT(MyColumn, 't/p');

I've created the thesaurus file, tsenu.xml in the correct location for the instance:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>insured</sub>
            <sub>insd</sub>
        </expansion>
        <expansion>
            <sub>t/p</sub>
            <sub>third party</sub>
        </expansion>
        <expansion>
            <sub>o/s</sub>
            <sub>other side</sub>
        </expansion>
        <expansion>
            <sub>p/p</sub>
            <sub>per person</sub>
        </expansion>
    </thesaurus>
</XML>

And loaded it using EXEC sys.sp_fulltext_load_thesaurus_file 1033;, however the query returns unpredictable results.

  • In 2013 Paul White seemed to indicate a lack of satisfying answer to a similar question. I can't think of any recent enhancements that would change this, unfortunately. As a work-around, you could try stripping out the characters in a separate column to search on, but I imagine that could be quite messy in the long run. – LowlyDBA Nov 6 '17 at 19:29
  • @LowlyDBA - hah, I even voted to close that question. Thanks! – Max Vernon Nov 6 '17 at 19:33
  • This talks about that approach as well. – Max Vernon Nov 6 '17 at 19:46
  • The workaround from MS is to use the LIKE operator: support.microsoft.com/en-us/help/200043/… – MJH Apr 29 '18 at 8:35
4

When you want to add words involving special characters that you want treated as a single word, you will want to create a custom dictionary.

Creating Custom Dictionaries for special terms to be indexed ‘as-is’ in SQL Server 2008 Full-Text Indexes

Be aware that custom dictionaries are limited in size, plus the larger the dictionary the slower fulltext can become.

Custom dictionaries are great when you have a limited set of words to add like AT&T or M&Ms which are words with special characters that might be used over and over that you wanted treated as a single word instead of the default word breaking behavior.

Custom dictionaries are not great for for words like part numbers (ex. JY-14562, PTW-14432, etc.) where every row in a table would result in a new entry in the custom dictionary. You could potentially have several hundred thousand or more unique part numbers. That would not be a good use for custom dictionary. If you need this type of behavior, you are better off writing your own word breaker. Creating word breakers is not well documented nor easy to create, but it does allow you to decide how you want words broken when you encounter special characters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.