 |
|
|
|
Next: Backup directory disappeared
|
| Author |
Message |
External
Since: Mar 13, 2006
Posts: 3
|
(Msg. 1) Posted: Wed Apr 19, 2006 8:14 pm
Post subject: Phase?
Archived from groups: microsoft>public>sqlserver>fulltext (more info?)
|
|
|
This is my first attempt to write a sp using a full-text search. It is a
straight forward search of a classified ad table where the search words can
be one or more words. It can be a word like 'Harley' or a phrase like 'for
sale'. The problem I am having is that I get an error if I pass in 'for
sale'. The message is Syntax error near 'sale' in the full-text search
condition 'for sale'. My sp looks like this:
PROCEDURE [dbo].[proc_publicSearchClassifiedAd]
@SearchWord varchar(255)
AS
BEGIN
SET NOCOUNT ON;
SELECT SUBJECT,HTMLBody FROM ClassifiedAd
WHERE CONTAINS((SUBJECT, HTMLBody), @SearchWord);
END
Looking a BOL, I found a reference to 'phrase', which is what I think I
need, but I am having problems with the syntax. The examples don't use a
variable like I am. Does anyone know the syntax? Am I missing anything
else? Thanks. |
|
| Back to top |
|
 | |
External
Since: Apr 21, 2006
Posts: 74
|
(Msg. 2) Posted: Fri Apr 21, 2006 4:55 am
Post subject: Re: Phase? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Phill wrote on Thu, 20 Apr 2006 11:55:02 -0700:
> That is exactly what i was looking for. You guys/girls are great.
*cough* Guys, both of us.
Dan |
|
| Back to top |
|
| |
External
Since: Apr 21, 2006
Posts: 74
|
(Msg. 3) Posted: Fri Apr 21, 2006 6:55 am
Post subject: Re: Phase? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hilary wrote on Fri, 21 Apr 2006 05:31:51 -0400:
> Thanks for the clarification Dan, your name always had me wondering;)
Heh. I've made the mistake in the past of assuming gender by name on a
mailing list, won't do that again 
Dan >> Stay informed about: Phase? |
|
| Back to top |
|
| |
External
Since: Jul 31, 2008
Posts: 2
|
(Msg. 4) Posted: Thu Jul 31, 2008 8:22 am
Post subject: Re: Phase? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hi Daniel,
I am using SQL Server 2005, full-text searching.
I was just wondering why it phrase matching works when querying directly in
analyzer, but then fails in a stored procedure. For example...
declare @holding varchar(200)
declare @keyword nvarchar(200)
Set @keyword='10,000 BC'
select @holding=char(34)+@keyword +char(34)
SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
FROM TBL_NLA_Articles
WHERE CONTAINS (ArticleText,@holding)
The above works well running in analyzer, but the below results in the
error: "Syntax error near ',' in the full-text search condition '10,000 BC'."
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchArticles]
@keyword nvarchar(200)
AS
BEGIN
Declare @holding nvarchar(200)
SELECT @holding = char(34)+@keyword+char(34)
SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
FROM TBL_NLA_Articles
WHERE CONTAINS (ArticleText,@keyword)
END
Executed in analyzer: SP_SearchArticles @keyword='10,000 BC'
What am i doing wrong!?!
thanks,
saleek >> Stay informed about: Phase? |
|
| Back to top |
|
| |
External
Since: Apr 21, 2006
Posts: 74
|
(Msg. 5) Posted: Thu Jul 31, 2008 4:45 pm
Post subject: Re: Phase? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
saleek wrote on Thu, 31 Jul 2008 08:22:03 -0700:
> Hi Daniel,
> I am using SQL Server 2005, full-text searching.
> I was just wondering why it phrase matching works when querying
> directly in analyzer, but then fails in a stored procedure. For
> example...
> declare @holding varchar(200)
> declare @keyword nvarchar(200)
> Set @keyword='10,000 BC'
> select @holding=char(34)+@keyword +char(34)
> SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
> FROM TBL_NLA_Articles
> WHERE CONTAINS (ArticleText,@holding)
> The above works well running in analyzer, but the below results in the
> error: "Syntax error near ',' in the full-text search condition '10,000
> BC'."
> set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
> ALTER PROCEDURE [dbo].[SP_SearchArticles]
> @keyword nvarchar(200)
> AS
> BEGIN
> Declare @holding nvarchar(200)
> SELECT @holding = char(34)+@keyword+char(34)
> SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
> FROM TBL_NLA_Articles
> WHERE CONTAINS (ArticleText,@keyword)
> END
> Executed in analyzer: SP_SearchArticles @keyword='10,000 BC'
> What am i doing wrong!?!
> thanks,
> saleek
Look at the CONTAINS clause in your Proc - you're using @keyword, not
@holding which you use in the Query Analyzer code.
--
Dan >> Stay informed about: Phase? |
|
| Back to top |
|
| |
External
Since: Jul 31, 2008
Posts: 2
|
(Msg. 6) Posted: Fri Aug 01, 2008 3:09 am
Post subject: Re: Phase? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
My apologies for wasting your time with that one!
On another note - is there a definitive way to handle ampersands (&) in
full-text queries?
Many thanks,
Khurram
"Daniel Crichton" wrote:
> saleek wrote on Thu, 31 Jul 2008 08:22:03 -0700:
>
> > Hi Daniel,
>
> > I am using SQL Server 2005, full-text searching.
>
> > I was just wondering why it phrase matching works when querying
> > directly in analyzer, but then fails in a stored procedure. For
> > example...
>
> > declare @holding varchar(200)
> > declare @keyword nvarchar(200)
> > Set @keyword='10,000 BC'
>
> > select @holding=char(34)+@keyword +char(34)
>
> > SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
> > FROM TBL_NLA_Articles
> > WHERE CONTAINS (ArticleText,@holding)
>
> > The above works well running in analyzer, but the below results in the
> > error: "Syntax error near ',' in the full-text search condition '10,000
> > BC'."
>
> > set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
>
> > ALTER PROCEDURE [dbo].[SP_SearchArticles]
>
> > @keyword nvarchar(200)
>
> > AS
> > BEGIN
> > Declare @holding nvarchar(200)
> > SELECT @holding = char(34)+@keyword+char(34)
> > SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
> > FROM TBL_NLA_Articles
> > WHERE CONTAINS (ArticleText,@keyword)
> > END
>
> > Executed in analyzer: SP_SearchArticles @keyword='10,000 BC'
>
> > What am i doing wrong!?!
>
> > thanks,
>
> > saleek
>
>
> Look at the CONTAINS clause in your Proc - you're using @keyword, not
> @holding which you use in the Query Analyzer code.
>
> --
> Dan
>
>
> >> Stay informed about: Phase? |
|
| Back to top |
|
| |
External
Since: Apr 21, 2006
Posts: 74
|
(Msg. 7) Posted: Fri Aug 01, 2008 1:32 pm
Post subject: Re: Phase? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Depends on what you are doing with them. Personally I pre-parse all search
strings on my site to strip and replace characters, eg.
'AT&T' would be left as it is
'AT & T' would be changed to 'AT AND T' as the assumption is that &
represents the word AND and I can't assume that the data, or the person
searching, is supposed to be AT&T.
Dan
saleek wrote on Fri, 1 Aug 2008 03:09:00 -0700:
> My apologies for wasting your time with that one!
> On another note - is there a definitive way to handle ampersands (&) in
> full-text queries?
> Many thanks,
> Khurram
> "Daniel Crichton" wrote:
>> saleek wrote on Thu, 31 Jul 2008 08:22:03 -0700:
>>> Hi Daniel,
>>> I am using SQL Server 2005, full-text searching.
>>> I was just wondering why it phrase matching works when querying
>>> directly in analyzer, but then fails in a stored procedure. For
>>> example...
>>> declare @holding varchar(200)
>>> declare @keyword nvarchar(200)
>>> Set @keyword='10,000 BC'
>>> select @holding=char(34)+@keyword +char(34)
>>> SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
>>> FROM TBL_NLA_Articles
>>> WHERE CONTAINS (ArticleText,@holding)
>>> The above works well running in analyzer, but the below results in
>>> the error: "Syntax error near ',' in the full-text search condition
>>> '10,000
>>> BC'."
>>> set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
>>> ALTER PROCEDURE [dbo].[SP_SearchArticles]
>>> @keyword nvarchar(200)
>>> AS
>>> BEGIN
>>> Declare @holding nvarchar(200)
>>> SELECT @holding = char(34)+@keyword+char(34)
>>> SELECT ID, Pub_Name, ArticleText, Publication_Date, ArticleID
>>> FROM TBL_NLA_Articles
>>> WHERE CONTAINS (ArticleText,@keyword)
>>> END
>>> Executed in analyzer: SP_SearchArticles @keyword='10,000 BC'
>>> What am i doing wrong!?!
>>> thanks,
>>> saleek
>> Look at the CONTAINS clause in your Proc - you're using @keyword, not
>> @holding which you use in the Query Analyzer code.
>> --
>> Dan >> Stay informed about: Phase? |
|
| Back to top |
|
| |
| Related Topics: | help to define a search criteria with FTS - Hi! I'm using FTS in MSSQL2000. 1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but not "bcda" or "aabcd" (always from the second letter). 2. the search column is a long string. the ...
CONTAINSTABLE - weird results - using "and not" - Hello everyone, I use full text search using containstable for search on my intranet site. Its been working wonderfully. However, I have recently been working on an upgrade to my search page to allow users to exclude words. When excluding words I use...
How to pass an SQLServer object to a method - Hi, I am writing some code in VB.NET and need to pass an SQLServer object to a method. How do I do that? What is the type of SQLServer object? I created it using : Dim oSQLServer = CreateObject("SQLDMO.SQLServer") and want to pass it to a ...
Rebuilding Full Text Catalog - Hi, I have created a db install app that will install all datatables, build full-text catalogs and add job scheduler to populate the catalogs several times a day. However, I would also like my catalogs to be rebuilt every midnight and repopulated again...
incremental population vs. change tracking - We are developing a Content Management System using both SQL Server 2000 and file system as repository. The files (content) are stored on disk and addition properties are stored in an SQL Server database. We'd like to provide Search functionality on.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
