SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Google-like Full Text Search


A Google-like Full Text Search

Author
Message
Mike C
Mike C
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9601 Visits: 1172
Comments posted to this topic are about the item A Google-like Full Text Search
Anipaul
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11837 Visits: 1407
This is a gr8 stuff.....



Oleg-461731
Oleg-461731
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 247
Hi Mike,
very useful article, especially code - thank you.

I tested code and found one problem.

If I entered phrase:

president -aluminium

it wokrs fine.

If phrase is:
-aluminium president

it generates syntax error, phrase cannot start from negation. I checked goole it understand correctly this kind of phrases.

How can I edit grammar to avoid this kind of errors?

Thank you again.
Regards,
Oleg.
Confucius247
Confucius247
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 40
More time talking about a c# compiler than the SQL. No talk of the limitations of the FTS.

Bit of a waste of time really.

From title of piece I was hoping for something about combining site useage stats stored in SQL with the FTS results to actually make something Google like. Google is not defined by its syntax, which few users use, but by the relevancy of its results.
marcosc
marcosc
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 41
Good article.
Actually, with Nautilus you can find a table just writing part of its name and once located you can find a record writing any word which is compared with like if it's a string and with "=" if its an integer.
The query is built for all the fields:

select top 10 *
from dbo.Customers (nolock)
where CustomerID like '%ana%' or CompanyName like '%ana%' or ContactName like '%ana%'...


Once the record is located, you can see it's related records (following the FK links).

There is also a "google like" search where you write a word or number and looks for it in the whole database.
Not much like google, but it works for developers.

Thanks, Marcos (http://sourceforge.net/projects/nautilus/)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: Administrators
Points: 222508 Visits: 19618
Bit of a waste of time really.


That's fairly rude of you. The description of the article seems to fit and there are plenty of people that would like to use Google syntax to build search on FTS.

Good job, Michael, in showing how FTS can be used in conjunction with other tools to help add search to your applications.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
davidbrook
davidbrook
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 22
I 2nd that. I think there will be a demand for this sort of thing. I'm in a hurry nowadays, grappling for a wire to hold on to, so I wasn't able to download the source and test it. But you can bet that I am going to bookmark it. dbSmile
Paul DB
Paul DB
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 258
While I am still trying to digest this excellent article, I think I found a slight error.

In The Grammar section, the article lists 7 rules. One of them is...

PrimaryExpression ::= Term
| '-' Term
| Phrase
| '(' OrExpression ')'
| '<' ( Term | Phrase )+ '>'



To more closely match the code in the Conversion Engine section, I think it should be more like the following.
Note the addition of the ExactExpression option.

PrimaryExpression ::= Term
| '-' Term
| ExactExpression
| '(' OrExpression ')'
| Phrase
| '<' ( Term | Phrase )+ '>'


A minor point, I know. I just want to make sure that I am not missing something here. BigGrin

Paul DB
Oleg-461731
Oleg-461731
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 247
Steve Jones - Editor (10/7/2008)
Bit of a waste of time really.


That's fairly rude of you. The description of the article seems to fit and there are plenty of people that would like to use Google syntax to build search on FTS.

Good job, Michael, in showing how FTS can be used in conjunction with other tools to help add search to your applications.


Yes Steve, you are right.
I work with FTS some years, and for me it is important because some customers would like to have this kind of syntx. Thanks.
Oleg-461731
Oleg-461731
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 247
marcosc (10/7/2008)
Good article.
Actually, with Nautilus you can find a table just writing part of its name and once located you can find a record writing any word which is compared with like if it's a string and with "=" if its an integer.
The query is built for all the fields:

select top 10 *
from dbo.Customers (nolock)
where CustomerID like '%ana%' or CompanyName like '%ana%' or ContactName like '%ana%'...


Once the record is located, you can see it's related records (following the FK links).

There is also a "google like" search where you write a word or number and looks for it in the whole database.
Not much like google, but it works for developers.

Thanks, Marcos (http://sourceforge.net/projects/nautilus/)


Sorry, but Full-Text search is another feature than LIKE operator.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search