x

SQL Full Text Search

Hi,

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.

For Example:

I have 6 parts (BOXD510MO, D510MO,BOXD510MOV, D510M,D510MOV,D510MOE). Every Part Numbers contain D510MO
I am using Query:

select * from Catalog.Product
where contains(Product.SalesTitle, '"* 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.

select * from Catalog.Product
where contains(Product.SalesTitle, '"* D510 *"')

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).

Regards,
more ▼

asked Aug 23, 2012 at 10:01 AM in Default

Mr.SEA gravatar image

Mr.SEA
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@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 BOXD510MO, BOX D510MO, BOX,D510MO, ABC BOXD510MO, ABC BOX D510MO would only yield the following

BOX D510MO -- D510MO IS A WORD STARTING WITH D510MO 
BOX,D510MO -- D510MO IS A WORD SEPARATED BY PUNCTUATION CHAR "," AND STARTING WITH D510MO 
ABC BOX D510MOVX -- D510MOVX IS A WORD STARTING WITH D510MO 
Hope it clarifies any ambiguities you have.
more ▼

answered Aug 23, 2012 at 01:40 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Dear Usman,

I understand what say, but please my case. When I search D510 then 135 records comes, those all are not starting with D510. There all records contains D510 including BOXD510MO, D510MO,BOXD510MOV, D510M,D510MOV,D510MOE and others... My question is why these all records are coming when I removed MO only. but when I check on some other sites of computer upgrades. the result is come perfectly. I don't know what are they doing. but thats very fast result and correct result.

Secondly, I don't know how visitor will search the text on site. of-course he will enter most useful characters.

If you have another solution for fast keyword search. So please let know.

I have not install anything extra on my server. Just windows 2008 R2 Server and Sql Server 2008 R2. yes, I had update windows once and there is no any SP installed.
Aug 23, 2012 at 02:25 PM Mr.SEA

Can you please try repopulating the Full Text Index. Something like

ALTER FULLTEXT INDEX ON [YOURINDEXNAME] START FULL POPULATION;
Aug 23, 2012 at 03:09 PM Usman Butt
Also please do not use * before the prefix term. Use "D510MO*" instead of "*D510MO* ".
Aug 23, 2012 at 03:25 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x577
x45

asked: Aug 23, 2012 at 10:01 AM

Seen: 720 times

Last Updated: Aug 23, 2012 at 03:25 PM