# Abbreviations in full text search index

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