Re: FT Search with nois words

From: Marius (marius_at_kanniets.nl)
Date: 10/20/04 

Date: 20 Oct 2004 02:11:21 -0700

But if I empty this file it will no longer ignore the noise words
(right?).

What I want is that the words will be ignored but the query will still
continue searching the database.
Here is the query I use to search:

--Begin
SELECT Total.TopicID,
       f.AuthorizationItemID,
       ft.TopicTitle,
       ft.TopicStatus,
       (SELECT MAX(PostDate) FROM ForumPost WHERE TopicID =
Total.TopicID) AS LastPost,
       (SELECT COUNT(*) - 1 FROM ForumPost WHERE TopicID =
Total.TopicID) AS NumberOfReplies,
       (SELECT UserFirstName + ' ' + UserLastName FROM Users WHERE
UserID = (SELECT UserID FROM ForumPost WHERE TopicID = Total.TopicID
AND PostDate = (SELECT MIN(PostDate) FROM ForumPost WHERE TopicID =
Total.TopicID))) AS Poster,
       f.ForumAfk,
       f.ForumName,
       f.ForumID,
       fc.CategoryName,
       SUM(Total.Score) AS Score
FROM
(
SELECT fp.TopicID,
       SUM(A.RANK) AS Score
       
FROM CONTAINSTABLE (ForumPost, PostMessage, @strSearchString) A
JOIN ForumPost fp ON A.[Key] = fp.PostID

GROUP BY fp.TopicID

UNION ALL

SELECT B.[Key] AS TopicID,
       B.RANK * 3 AS Score

FROM CONTAINSTABLE (ForumTopic, TopicTitle, @strSearchString) B
) AS Total
JOIN ForumTopic ft ON Total.TopicID = ft.TopicID
JOIN ForumCategory fc ON ft.CategoryID = fc.CategoryID
JOIN Forum f ON fc.ForumID = f.ForumID

GROUP BY Total.TopicID, f.AuthorizationItemID, ft.TopicTitle,
ft.TopicStatus, ft.CategoryID, f.ForumAfk, f.ForumName, f.ForumID,
fc.CategoryName
ORDER BY Total.Score DESC
--End

The query it self works perfectly as far is I tested. But a
searchstring like 'test AND is' fails. Looking for a string like
'"test is"' does work but then it wil look for that exact string and
that's not the answer i'm looking for.

A option is ofcourse to remove the noise words from the string in my
code so they never end up in the store procedure. But I realy don't
like that solution so I hope there is an other way.

Marius

"Kevin Lewis" <lewis.kevin@myfloridahouse.com> wrote in message news:<eoyE5eitEHA.4044@TK2MSFTNGP09.phx.gbl>...
> SQL Server has a config file that specifies all the noise words it ignores
> in full text searches. It is located here:
>
> Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config\noise.eng
>
> I'm not sure of the specifics on how you get SQL Server to recognize an
> update to this file.
>
> "Marius" <marius@kanniets.nl> wrote in message
> news:65032a84.0410190111.196b8638@posting.google.com...
> > Hello,
> >
> > Is there a way to make it possible to search for the following
> > searchstring with containstable?
> >
> > "test AND is"
> >
> > what i mean is that it will ignore the word "is" but will continu
> > searching for "test"
> >
> > thnx in advance,
> >
> > Marius

Relevant Pages

  • Re: FT Search with nois words
    ... Looking for a SQL Server replication book? ... > What I want is that the words will be ignored but the query will still ... Looking for a string like ... > A option is ofcourse to remove the noise words from the string in my ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Execution of a full-text operation failed. A clause of the query contained only ignored words.
    ... Paul's search string from his initial posting including a long string of ... The above can be converted to a contains clause using the pubs table ... this is less about searching on noise words in the noise word ... Why would someone want to parse a search string to ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Execution of a full-text operation failed. A clause of the query contained only ignored words.
    ... searching on asterisks will generate this error. ... > Paul's search string from his initial posting including a long string of ... this is less about searching on noise words in the noise word ... > "Hilary Cotter" wrote in message ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS and hyphens
    ... for some reason. ... The full string I was seaching for with "CONTAINS (SEARCHCOL,' ... not only removing "A" from the noise words but also the numbers. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: a clause of the query contained only ignored words
    ... Purely for diagnostic purposes could you run this script on your SQL Server ... to find these noise words in your searches you will have to rebuild>> your catalog. ... > same error but now it works correctly, so I'm pretty sure I edited the> correct noiselist and rebuilt the catalog correctly. ...
    (microsoft.public.sqlserver.fulltext)
Loading