Fulltext Search conflicting with currency Keyword

VENKAT RAMALINGAM 1 Reputation point
2021-06-21T03:26:29.567+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,082 questions
0 comments No comments
{count} votes

8 answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2021-06-22T09:30:29.063+00:00

    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.

    108010-screenshot-2021-06-22-172855.jpg


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


  2. Erland Sommarskog 103.7K Reputation points MVP
    2021-06-22T19:55:55.153+00:00

    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.

    0 comments No comments

  3. VENKAT RAMALINGAM 1 Reputation point
    2021-06-23T14:14:59.863+00:00

    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)