RE: FULL TEXT SEARCH

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ahsan (Ahsan_at_discussions.microsoft.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 05:45:03 -0700

Ah!!!
Interesting thing I have found
Maximum length I have found in the data for fullname colum is 24
Max(len(fullname))
returns: 26

when I changed the length of the variable @vtemp to 23, it works (amazed why so), well I have posted the whole code with sample table script and data.

just change the @vtemp length
NOT WORKING: declare @vTemp as varchar(50)
WORKING: declare @vTemp as varchar(23)

error was:
Server: Msg 7631, Level 15, State 1, Line 22
Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson
'.

now the lenght of this 'Kevin Durban- Jackson
' is 26 :)
I am wondering whats goin on, you ask me to check the data type and length and i checked it, it was right, but still I am wondering whats going on!!!, is there a limitation on the length of the variable one can use in freetext or contain ?

"Ahsan" wrote:

> John,
> data type and length are same but I am still getting this error, I am using Sql server 2000 standard edition on windows 2000
> ERROR IS:
> Server: Msg 7631, Level 15, State 1, Line 19
> Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson
> '.
>
> SELECT * From temp
> QUERY:
> SET QUOTED_IDENTIFIER OFF
> declare @vTemp as varchar(50)
>
> SET @vTemp = (SELECT ltrim(rtrim(FULLNAME)) FROM TEMP where incontact=46)
> --Set @vTemp = '"' + @vTemp + '"'
> --print @vTemp
> SELECT distinct * From temp
> where
> freetext(fullname,@vTemp)
> and freetext(address,'14 Marchmont Rd') AND contains(zip,'"SM6 9NU"')
>
> TABLE SCRIPT:
> CREATE TABLE [dbo].[Temp] (
> [ID] [int] NOT NULL ,
> [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FullName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InContact] [numeric](18, 0) NULL ,
> [Gender] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecentID] [int] NULL
> ) ON [PRIMARY]
> GO
>
>
> SAMPLE DATA:
> (this is exported from sql server export facility)
> 1,14 Marchmont Rd
> ,SM6 9NU
> ,Kevin Durban- Jackson
> ,46,Male
> ,
> 2,14 Marchmont Road
> ,SM6 9NU
> ,Kevin Durban - Jackson
> ,42,Male
> ,
> 3,14 Marchmont Road
> ,SM6 9NU
> ,Kevin Durban- Jackson
> ,1,Male ,
> 4,14 Marchmont Rd
> ,SM6 9NU,Kevin Durban-jackson
> ,3,Male ,
> 5,14 Maplin Park
> ,SL3 8XY
> ,Jo Rowden
> ,1,Female
> ,
> 6,14 Maplin Park
> ,SL3 8XY
> ,Nige Bacon
> ,1,Male ,
> 7,14 Maplin Drive
> ,SK2 5XJ
> ,Viv Nash
>
> ,2,Male ,
> 8,14 Maple Wood
> ,NN10 0UN
> ,Kevin Durban-jackson
>
> ,1,Female ,
>
> Could you give it a try by creating the sample table in your sql server john?
> many thanks in advance
>
> "John Kane" wrote:
>
> > Ahsan,
> > Since this is the fulltext newsgroup, I thought I'd provide an example of a successful variable assignment for your question:
> >
> > declare @keyword varchar(100), @count int
> > set @keyword = 'MyActivityList'
> > set @count = (select count(*) from jtkane_ASP_Procs where contains(*,@keyword))
> > print @count -- returns: 1
> >
> > A non-FTS solution using TOP 1 <valid_column_name> example:
> >
> > declare @SearchWord varchar(100)
> > SET @SearchWord = (select top 1 ASPFile from jtkane_ASP_Procs)
> > print @SearchWord
> > -- returns: MyActivityList.cs
> >
> > In your example below your variable @SearchWord should be declared as the same datatype and lenght as the column named: Fields1 from your table named: TableName.
> >
> > Hope this helps.
> > Regards,
> > John
> >
> >
> > "Ahsan" wrote:
> >
> > > Microsoft Says:
> > > This example uses a variable instead of a specific search term.
> > >
> > > USE pubs
> > > GO
> > > DECLARE @SearchWord varchar(30)
> > > SET @SearchWord ='Moon'
> > > SELECT pr_info FROM pub_info WHERE FREETEXT(pr_info, @SearchWord)
> > >
> > >
> > >
> > > at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2juc.asp
> > >
> > > but what I want is to assign a value to the variable @SearchWord from a query like
> > >
> > > SET @SearchWord = (SELECT top 1 Fields1 from TableName)
> > >
> > > which seems to be not working and given a error.
> > > Server: Msg 7631, Level 15, State 1, Line 15
> > > Syntax error occurred near '''. Expected '_STRING' in search condition 'Some Value
> > > '.
> > >
> > > where 'Some value' will be the value selected by the query 'SELECT top 1 Fields1 from TableName', so can any one help me out that how to use a variable which has been asigned a value from a query not explicitly assigned like SET @SearchWord ='Moon' ?, I have tried every combination of wraping the variable with single and double qoutes but its not working
> > >
> > > many thanks
> > >
> > >
> > >

Relevant Pages

  • Re: Scratchy the Squirrel..... Dead!
    ... That probably came out a little harsher than I meant, Kevin. ... For the past week every major TV and radio outlet has been full of the sage pronouncements of the hordes of promoters, publicists, agents, close friends and distant acquaintances and myriad general fart-catchers who push the concept that Jackson was a genius and the acme of American cultural development. ... Taste is a personal thing, and while I understand there are millions who enjoy and admire his work, I find most of it boring and pretentious. ... over the course of your life you will not have generated several times that 2.5 mil. ...
    (rec.music.makers.guitar.acoustic)
  • Re: A peace offering
    ... Jackson wrote: ... Hello Kevin, if you could see some of the emails I receive from people ... Disturbing. ...
    (rec.music.classical.guitar)