FULL TEXT SEARCH

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/de...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
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/de...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
>
>
|||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:
[vbcol=seagreen]
> 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:
?, I have tried every combination of wraping the variable with single and double qoutes but its not working[vbcol=seagreen]
|||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:
[vbcol=seagreen]
> 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:
?, I have tried every combination of wraping the variable with single and double qoutes but its not working[vbcol=seagreen]