Re: full-text contains * issue

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Richard S Yeo (Yeo_at_discussions.microsoft.com)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 10:01:54 -0700

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

Relevant Pages

  • Re: Parameters Question
    ... Here's how to do parameterize queries in Microsoft Access 2003. ... Access 2003 database than it would be for SQL Server using OleDB. ... using concatenation to build dynamic SQL SELECT...statement. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Access SQL to T-SQL
    ... part of my concatenation. ... pipe symbol between them, like this: ... >> send the SQL string directly to the database instead ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Query Help
    ... Lists -Field Concatenation, One Field to Itself for string ... SQL 2000 ... the records on a separate row each. ...
    (microsoft.public.sqlserver.mseq)
  • Re: Guids and Nullness
    ... Use '&' for concatenation instead of '+' ... propagates nulls and does implicit type conversion. ... In Jet SQL, not using Access, use IIF instead of ISNULL ... > either the dog's name or an empty or null string to use in beautifying my ...
    (microsoft.public.access.formscoding)