Contains() function falters with strings of number

问题:

For some background information, I'm creating an application that searches against a couple of indexed tables to retrieve some records. It isn't overtly complex to the point of say Google, but it's good enough for the purpose it serves, barring this strange issue.

I'm using the Contains() function, and it's going very well, except when the search contains strings of numbers. Now, I'm only passing in a string -- nowhere numerical datatypes being passed in -- only characters. We're searching against a collection of emails, each appended with a custom ID when shot off from a workflow. So while testing, we decided to search via number strings.

In our test, we isolated a number 0042600006, which belongs to one and only one email subject. However, when using our query we are getting results for 0042600001, 0042600002, etc. The query is this as follows (with some generic columns standing in):

SELECT description, subject FROM tableA WHERE CONTAINS((subject), '0042600006')

We've tried every possible combination: '0042600006*', '"0042600006"' and '"0042600006*"'.

I think it's just a limitation of the function, but I thought this would probably be the best place for answers. Thanks in advance.

回答1:

Asked this same question recently. Please see the insightful answer someone left me here

Essentially what this user says to do is to turn off the noise words (Microsoft has included integers 0-9 as noise in the Full Text Search). Hope you can use this awesome tool with integers as I now am!

回答2:

try to add language 1033 as an additional parameter. that worked with my solution.

SELECT description, subject FROM tableA WHERE CONTAINS((subject), '0042600006', language 1033)

回答3:

try using SELECT description, subject FROM tableA WHERE CONTAINS((subject), '%0042600006%')