Fulltext Search conflicting with currency Keyword

Question

We have a situation where fulltext search index column has one of the thailand currency word 'THB' conflicting with the regular search and not resulting in the query outcome ,when the command CONTAINS is used.Instead of treating as regular word , it treats it as currency.But when we change the word to lower case in the database, it finds it.I want the search to work as regular word search instead of sensing it as a currency.
Any help is appreciated to resolve this issue.
VR

Answer 1

Hi @VENKAT RAMALINGAM ,

It seems you misunderstood the CONTAINS function.

Quote from MS document CONTAINS (Transact-SQL).

Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server.

CONTAINS can search for:

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

Below is a test in my environment.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 2

I don't think there is much can be done about this. If you run:

SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id(), object_id('slaskis'))

You will see that 1111 THB and 2222 SEK is considered to be "words", whereas 1111 ABC is not.

Answer 3

Thanks for the reply Viorel and love the approach!
But there following challenges I have on this solution:
The table is huge , atleast 400 million records and has space constraints
We basically have a indexed view created on top of this base table on which the Full text Index has been created and searches are based on a combined field( concatenate multiple columns into one search column on the indexes view)