Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / September 2004

Tip: Looking for answers? Try searching our database.

request for sample program for fulltext search

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
VpUser - 22 Sep 2004 03:13 GMT
hi...

i'm newbie here... can i have a sample program for fulltext search.

thanx!
John Kane - 22 Sep 2004 04:41 GMT
VpUser,
Well, could you be more specific? SQL Server 2000 Full-text Search (FTS)
uses pure T-SQL such as CONTAINS or FREETEXT, for example:

select * from pub_info where CONTAINS(*,'books')

or if you want a SQL-DMO sample program, you can checkout SQL 2000 BOL title
"SQL-DMO Examples: Full-text Indexing"

More info would be helpful. What are you looking to do with the sample
program?
Regards,
John

> hi...
>
> i'm newbie here... can i have a sample program for fulltext search.
>
> thanx!
VpUser - 22 Sep 2004 05:05 GMT
John,

Thanks John! It's very great example. I'm using SQL Server 2000 FTS.

1. May i know what different between CONTAINS and FREETEXT?

eg.

select * from pub_info where CONTAINS(title,'booktitle')

and

select * from pub_info where FREETEXT(title,'booktitle')

Its returns same result.

2. When i try this query:

==> select * from pub_info where CONTAINS(title,'book and title')

It's giving me the error. Error abt noisy words...

May i enter noisy word in the search? how do i know the noisy word by using
SQL statement(not from text file).

regards,

VpUser
John Kane - 22 Sep 2004 06:51 GMT
You're welcome, VpUser,
1. May i know what different between CONTAINS and FREETEXT?
A. CONTAINS and CONTAINSTABLE will return rows that contain the exact word
that you are searching for while FREETEXT or FREETEXTTABLE will return the
exact word and if present, words that "match the meaning and not the exact
wording of the words in the search condition" (from SQL 2000 BOL). So while
in the small table pub_info, the results would be the same for your search
word booktitle, while in larger tables with more diverse text, FREETEXT
would often return more rows than CONTAINS when searching for the same word.

2. The correct sntax for the table pub_info than does not have a column
called "title", would be:
select * from pub_info where CONTAINS(*,'book and title')

When I execute the above query on Win2003, it returns 0 rows and no errors.
However, if I change title in the search condition, to "between" a
US_English noise word:

select * from pub_info where CONTAINS(*,'book and between')

This returns error  Msg 7619 ".. A clause of the query contained only
ignored words". This can be avoided by removing "between" from the
US_English noise word file noise.enu that is located under
\FTDATA\SQLServer\Config where you have SQL Server 2000 installed. You can
open and edit this file with notepad.exe, but to save it you will need to
stop the "Microsoft Search" service and then run a Full Population.
Additionally, you can alter the query using quotes or parse the noise word
out via pre-processing before passing it to a SQL Server contains query. I'd
recommend that you review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well as KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

3. May i enter noisy word in the search? how do i know the noisy word by
using SQL statement(not from text file).
A. Yes, but you will need to parse the noise word into phrases or remove it
to avoid the error. You can also use BULK INSERT and import the noise word
file (noise.enu text file) into a SQL table and then use that table as a
lookup table to determine if the searcher enters a noise word.

Regards,
John

> John,
>
[quoted text clipped - 24 lines]
>
> VpUser
VpUser - 22 Sep 2004 10:06 GMT
John,

Thanks your clear explanation. It's very clear and "user friendly", easy for
me to pick up.

Well... i still have one question abt Contains.
I try to run this query in SQL server it giving me the error.
Error ==> select * from pub_info where CONTAINS(*,'yellow book')
it return me the error...
If  I enter
OK==> select * from pub_info where CONTAINS(*,'yellow and book')
How do i the query if i want to find "yellow book" exact word in the query?

best regards,
VpUser
Hilary Cotter - 22 Sep 2004 15:20 GMT
wrap your search phrase in double quotes

select * from pub_info where CONTAINS(*,'"yellow book"')

that's a single quote, followed by a double quote, followed by yellow book
followed by a double quote followed by a single quote

> John,
>
[quoted text clipped - 13 lines]
> best regards,
> VpUser
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.