6

I have an SQL Server 2008 Database with a Full Text Index set to one table column used for search.

When trying to perform a query against the table using the CONTAINS clause with the following criteria: "003", it ignores the leading zeros and returns all rows matching "3".

We are trying to take into account the leading zeros for the search, any ideas?

(The stoplist for the full text is empty).

UPDATE: The Query

SELECT * FROM Table
WHERE CONTAINS(SearchIndex, '"003*"')
luisporras
  • 61
  • 3
  • What is the output of the query? You say that it returns all rows that match "3", but does it also return the ones with the leading zeros? Did the ordinary SELECT * FROM Table WHERE SearhIndex LIKE '003%' work? – TheBoyan Dec 12 '11 at 20:04
  • Indeed returns the ones with leading zeros and the ones without them. – luisporras Dec 12 '11 at 20:17
  • @bojansjr Indeed returns the ones with leading zeros and the ones without them. And the ordinary LIKE solution didn't work for us. – luisporras Dec 12 '11 at 20:29

3 Answers3

0

I ran into this same problem and found the problem is with the way the full-text parser handles leading 0's. This post helped me understand better. You can see the issue yourself if you use sys.dm_fts_parser to examine how the full-text parser would parse a number with leading 0's.

I really wish that Microsoft would allow this 'feature' to be turned off. The work-around mentioned in other answers works well, however, is doesn't easily allow for matching based on multiple columns like the various full-text search functions do.

cjbarth
  • 4,189
  • 6
  • 43
  • 62
0

A possible solution to this question is to use a Custom Dictionary. More details are available in this answer: https://stackoverflow.com/a/22704439/1899.

Community
  • 1
  • 1
Phil Haselden
  • 2,876
  • 3
  • 30
  • 25
0

I had a similar problem like this a while ago.

Perhaps one approach could be to select several results with the full-text search and then SELECT the specific one from those results. But maybe there could be a better solution to this.

I tried this approach and it actually worked. It also works a lot faster then to just SELECT the value.

TheBoyan
  • 6,802
  • 3
  • 45
  • 61