I have a Search box on my site at top. Where visitor can enter any keyword. I have Full Text Search enabled on my DB and index the Columns. I have one Columns SalesTitle in one of Table Catalog.Product, which contains almost our required keyword, like ProductNumber, Description, BrandName, Product Size, etc...
I am using Contains, but this contains does not give me correct result. What's happening like let me explain you.
I have 6 parts (BOXD510MO, D510MO,BOXD510MOV, D510M,D510MOV,D510MOE). Every Part Numbers contain D510MO
But this query result come only 4 parts. which starts with D510MO. I thought the contains brings with start numbers only.
But when I changed my text like: D510MO to D510 the result comes 135 records. I check all result contains D510 any where in SalesTitle, It has puzzled me.
I know there are some syntax of contains full text search. But my query is same in both cases but result is different
Can you please help me, where am I making mistake. How do i make my search with keyword exactly. Is there any patch which i need to install on my server. I have researched a lot but I did not find anything new....
I am using SQL Server 2008 R2 ver (10.5.x.x).
@Mr.SEA What Service Pack / Hotfix / CU are you running? You did not mention. So If it is kind of a bug (which I doubt), then we can try to simulate accordingly.
But if I see your code, you are doing prefix_term search. AFAIK, you rightly understood that you would get back only those records which are starting from the criteria stated. Hence, " D510MO " search would be working the same as "D510MO *" search.
Now if you would decrease the number of prefix characters, then there is more chance of getting more records as there could be more words starting with D510 as compare to D510MO and hence you are getting 135 records.
The only thing you need to remember that the search would be for a whole word starting with "D510MO", which must be separated by a space OR punctuation characters like ",?" etc. Hence a search for "D510MO" against values
Hope it clarifies any ambiguities you have.