CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlfull-text-search
EHorodyski
EHorodyski

Reputation: 794

Contains() function falters with strings of numbers?

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.

Upvotes: 3

Views: 1904

Answers (3)

gsharp
gsharp

Reputation: 27927

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)

Upvotes: 1

Rachael
Rachael

Reputation: 1995

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!

Upvotes: 1

Naval
Naval

Reputation: 344

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

Upvotes: -1

Related Questions