Re: full-text contains * issue
From: John Kane (jt-kane_at_comcast.net)
Date: 09/13/04
- Next message: Mike Davies: "Re: Full Text - Index Population - HDD Access"
- Previous message: John Kane: "Re: Full text index not populating correctly"
- In reply to: Richard S Yeo: "Re: full-text contains * issue"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 13 Sep 2004 11:20:18 -0700
Richard,
You may also want to consider using a stored proc for this situation as it
will perform better as the query plan will be re-used and the following
stored proc can be very flexible in regards to what you want to search on
including NOT conditions...
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @vcSearchText varchar(7800))
AS
declare @s as varchar (8000)
set @s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@vcSearchText+''''+')'
exec (@s)
go
-- returns 2 rows as expected
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- returns 0 rows as expected
EXEC usp_FTSearchPubsInfo '("books" and not "publisher")'
go
-- returns 2 rows as expected
EXEC usp_FTSearchPubsInfo '("book*") and not ("food" or "work")'
go
Regards,
John
"Richard S Yeo" <Richard S Yeo@discussions.microsoft.com> wrote in message
news:370C3F83-994A-4EC6-B8C6-E329378EA002@microsoft.com...
> Hilary
>
> Title= isn't part of the data just intended to show what that field
returned.
>
> There must be a bug with the contains(*,... implementation
>
> This is what I have done to get around this issue.
>
> I tried a Derived/Computed column, i.e. column on Job table which
contained
> no data but was concatenation of the fields I wanted to index. MSSearch
> didn't like this and said field did not belong to table.
>
> Thought about using a View but views do not appear to support Full-Text
>
> Decided against adding the concatenated data to the Job table as it would
> reduce the number of records per page.
>
> I have created a new database table JobFullText which has two fields
> JobID(PK) and FullTextData, i.e. 1:1 with Job
>
> FullTextData field is a concatenation of Title, Description, Location and
> Reference.
>
> I will keep the JobFullText.FullTextData up to date via JobSave code
rather
> than trigger as I will do some conversions.
>
> So far performance looks great in fact it looks a lot better!!!
>
> This is the SQL
>
> select JobID, CreatedDateTime, JobType, Title, Description, Location,
> Salary, Reference
> from Job inner join containstable(JobFullText, FullTextData, 'asp and not
> aspdotnet') as FT on Job.JobID = FT.[Key]
> order by FT.Rank desc, JobID desc
>
> Is the SQL OK?
>
> Rich
>
>
> "Hilary Cotter" wrote:
>
> > Are you searching text or html files? Sometimes if there are formatting
> > characters in front of or after a word this word will not be searched
on.
> > Perhaps this is what you are running into.
> >
> > I notice that the = sign does this.Both title and aspdotnet will not
show up
> > in searches. However searches on title=aspdotnet will show up.
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "Richard S Yeo" <Richard S Yeo@discussions.microsoft.com> wrote in
message
> > news:BCFA7628-93C3-4C42-A8E3-5BFEDBA54991@microsoft.com...
> > > Hilary
> > >
> > > Thanks for replying.
> > >
> > > I have tried the following
> > >
> > > Select * from Job where Contains(*, '"ASP" and not "ASPDOTNET"')
> > >
> > > as you suggested but it returns 186 rows and some still contain
ASPDOTNET,
> > > e.g.
> > > Title=ASPDOTNET Developer - London
> > >
> > >
> > > Rich
> > >
> > >
> > > "Hilary Cotter" wrote:
> > >
> > > > try this
> > > >
> > > > Select * from Job where Contains(*, '"ASP" and not "ASPDOTNET"')
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > >
> > > > "Richard S Yeo" <Richard S Yeo@discussions.microsoft.com> wrote in
> > message
> > > > news:A9D46A9E-7CF6-4425-98DC-EC2E282174F7@microsoft.com...
> > > > > Can anybody explain the following please?
> > > > >
> > > > > The Catalog is comprised of the following fields.
> > > > >
> > > > > Title
> > > > > Description
> > > > > Location
> > > > > Reference
> > > > >
> > > > > Select * from Job where Contains(*, 'ASP and not ASPDOTNET')
> > > > >
> > > > > For some reason it returns rows containing ASPDOTNET, e.g.
> > > > >
> > > > > Title=ASPDOTNET Developer - London
> > > > > Description=ASPDOTNET Developer required by a large organisation
based
> > in
> > > > > London. You must have a firm background in ASPDOTNET development
as
> > well
> > > > as
> > > > > HTML. This role is for immediate start.
> > > > > Location=London
> > > > > Reference=DD/LM/ASP/LONDON
> > > > >
> > > > > If replace * in contains with the following the number to the
right is
> > the
> > > > > number of rows returned
> > > > > -- * 186
> > > > > -- Title 37
> > > > > -- Description 169
> > > > > -- Location 0
> > > > > -- Reference 2
> > > > >
> > > > > select *
> > > > > from job
> > > > > where
> > > > > (title like '%asp%' and title not like '%aspdotnet%') and
> > > > > (description like '%asp%' and description not like '%aspdotnet%')
> > > > > -- rows returned = 23
> > > > >
> > > > >
> > > > > Select @@Version
> > > > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> > 14:22:05
> > > > > Copyright (c) 1988-2003 Microsoft Corporation Developer Edition
on
> > > > Windows
> > > > > NT 5.1 (Build 2600: Service Pack 1)
> > > > >
> > > > > Select @@language
> > > > > Us_english
> > > > >
> > > > > I have tried English (United States), English (United Kingdom) and
> > > > Neutral,
> > > > > each changed followed by full population and the number of rows
> > returned
> > > > each
> > > > > time is the same.
> > > > >
> > > > > Any help will be most welcome
> > > > >
> > > > > Rich
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >
- Next message: Mike Davies: "Re: Full Text - Index Population - HDD Access"
- Previous message: John Kane: "Re: Full text index not populating correctly"
- In reply to: Richard S Yeo: "Re: full-text contains * issue"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
