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.
LIKE
operator: support.microsoft.com/en-us/help/200043/… – MJH Apr 29 '18 at 8:35