How to get desired result with full-text search in SQL Server through thesaurus?

Question

I am using SQL Server 2019. I define thesaurus in language XML in SQL Server full text search.

<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
    <expansion>
        <sub>DNA typing</sub>
        <sub>genetic fingerprinting</sub>
    </expansion>
</thesaurus>
</XML>

As thesaurus and prefix form (*) can not be used inside contains statement such as

SELECT * 
FROM new_framework_text ft 
WHERE CONTAINS (ft.text, N'FORMSOF (THESAURUS,"DNA typing*")')

Usually, I use this query for get result, but it is not actually return desired result.

SELECT * 
FROM new_framework_text ft 
WHERE CONTAINS (ft.text, N'FORMSOF (THESAURUS, "DNA typing") OR "DNA typing*"')

Above, query will return rows with 'DNA typing', 'genetic fingerprinting' and 'DNA typing*'.

It can not get the rows start with thesaurus word which is 'genetic fingerprinting*'.

I do not want to write static code in project, because there will be a lot of thesaurus in future.

I searched the internet, but can not find any valid answer.

How can I achieve to get needed records with just only query?

Full-Text is primarily designed as a similar-word search, it's not really designed for wildcard searches — Charlieface, Jul 16 '21 at 16:21
@Charlieface So it means there is no way to do it except write static code? — Samir Allahverdi, Jul 16 '21 at 16:33
You could build a thesaurus yourself perhaps? `Thesaurus` table with each word referencing synonyms, then you can do a search `LIKE synonym + '%'` — Charlieface, Jul 16 '21 at 16:53
@Charlieface Actually, there are a lot of data. For this case, LIKE is not recommended. — Samir Allahverdi, Jul 16 '21 at 17:52
I wouldn't say that: `LIKE` with a prefix and a wildcard only at the end is fine. The problems start when the wildcard is at the beginning — Charlieface, Jul 18 '21 at 00:51
@Charlieface I did not know it, can you reference to this, please? — Samir Allahverdi, Jul 18 '21 at 12:52
https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning also https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/ — Charlieface, Jul 18 '21 at 12:59

How to get desired result with full-text search in SQL Server through thesaurus?

0 Answers0