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.
12,676 questions
0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.1K Reputation points
    2021-06-22T20:15:07.76+00:00

    Maybe you can use your finding as a workaround. Add a calculable column like this:

    alter table slaskis add mytext2 as lower(mytext)

    It does not have to be populated. Then add the full-text index to mytext2 instead of mytext. Probably this will not require more space.

    Then use mytext2 for searches and mytext for displaying the results.

    Both seem to work:

    SELECT * FROM slaskis WHERE CONTAINS(mytext2, 'THB')
    SELECT * FROM slaskis WHERE CONTAINS(mytext2, '1111')

    1 person found this answer helpful.
  2. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2021-06-21T08:14:56.127+00:00

    Hi @VENKAT RAMALINGAM ,

    Full-text queries are not case-sensitive.

    What is the data type of this column? A full-text index includes one or more character-based columns in a table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM.

    Refer to MS document Full-Text Search.

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

    0 comments
  3. VENKAT RAMALINGAM 1 Reputation point
    2021-06-21T11:46:46.627+00:00

    The fulltext search indexed column is of varchar datatype.I understand Full text queries aren't case sensitive, but when I change the word case from 'TBH' to 'tbh' I get the search results using CONTAINS command.Basically,I think it invalidates the inbuilt currency recognition and acts like a regular string search when changed to lower case.

    0 comments
  4. Erland Sommarskog 100.9K Reputation points MVP
    2021-06-21T22:04:03.147+00:00

    Hm, this seems to return THB: 

    CREATE FULLTEXT CATALOG katha

CREATE TABLE slaskis(id int CONSTRAINT pk_slaskis PRIMARY KEY (id), mytext varchar(200))
INSERT slaskis (id, mytext)
   VALUES(1, 'Detta är en text med THB'), (2, 'Detta är en text med SEK')

CREATE FULLTEXT INDEX ON slaskis (mytextLANGUAGE 1053) KEY INDEX pk_slaskis  ON Katha

SELECT *FROM slaskis WHERE CONTAINS(mytext, 'THB')

    Do you have a similar repro to demonstrate your issue?

    0 comments
  5. VENKAT RAMALINGAM 1 Reputation point
    2021-06-21T22:33:16.693+00:00

    Thanks for the example :)
    please try the same with below changes. I have numbers added before the currency symbol and query the numbers and see what you get.
    And try the same with inserting lower case.

    CREATE FULLTEXT CATALOG katha
    CREATE TABLE slaskis(id int CONSTRAINT pk_slaskis PRIMARY KEY (id), mytext varchar(200))
    SELECT * FROM slaskis
    INSERT slaskis (id, mytext)
    VALUES(1, '1111 THB'), (2, '2222 SEK')
    CREATE FULLTEXT INDEX ON slaskis ([mytext] LANGUAGE 'English') KEY INDEX pk_slaskis ON Katha
    SELECT *FROM slaskis WHERE CONTAINS(mytext, '"1111"')

    0 comments