 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / August 2008Phase?
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. try something like this: alter PROCEDURE [dbo].[proc_publicSearchClassifiedAd] @SearchWord varchar(255) AS BEGIN SET NOCOUNT ON; declare @holding varchar(200) select @holding=char(34)+@SearchWord +char(34) select SUBJECT,HTMLBody FROM ClassifiedAd WHERE CONTAINS((SUBJECT,HTMLBody ), @holding); END  SignatureHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > 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 [quoted text clipped - 18 lines] > variable like I am. Does anyone know the syntax? Am I missing anything > else? Thanks. Thanks Hilary. It works if I pass in 'for sale', but if I pass in 'harley for sale' I don't get any results. My data has a record that has "Motor cycle for sale" in the Subject column and "harley-davidson Motor cycle Sale. Year 2006" in the HTMLBody column. I would expect this row to be returned. Also, I noticed you defined the @holding variable as varchar(200). Is 200 the "magical" length for search strings? Thanks for your help. > try something like this: > [quoted text clipped - 31 lines] > > variable like I am. Does anyone know the syntax? Am I missing anything > > else? Thanks. The reason it doesn't work when you use 'harley for sale' is that the char(34) adds double quotes to the string, turning it into an exact phrase match search, so you're actually searching for "harley for sale". You're better off adding single quotes to the search parameter in the proc, and make sure in the calling code that you add the appropriate double quoting for exact phrase searching as required. 'harley and for and sale' will be split into the 3 words and searched in any order in any column specified (although I think FTS still requires all words to be in the same column, so still might not find the row) "harley for sale" will not be split, and that exact phrase will need to exist in a column being searched. Your original code errors because you cannot search for multiple words without an operator between them, eg. 'for sale' will fail with the error you saw as it becomes CONTAINS((SUBJECT, HTMLBody), ' for sale ') which is not valid syntax for CONTAINS 'for and sale' will work, looking for both words. Hilary's code makes your search into "for sale", which works because it ends up being CONTAINS((SUBJECT, HTMLBody), ' "for sale" ') Ideally, you need to build a routine to take input and work into the appropriate form for passing into CONTAINS. It's often not a simple task - I've got a DLL routine on my own site dedicated to it, handling various combinations of quoting, parentheses, and operators. Dan Phill wrote on Thu, 20 Apr 2006 07:47:02 -0700: > Thanks Hilary. It works if I pass in 'for sale', but if I pass in 'harley > for sale' I don't get any results. My data has a record that has "Motor [quoted text clipped - 4 lines] > Also, I noticed you defined the @holding variable as varchar(200). Is 200 > the "magical" length for search strings? Thanks for your help. >> try something like this: >> [quoted text clipped - 26 lines] >>> a variable like I am. Does anyone know the syntax? Am I missing >>> anything else? Thanks. Thanks for the insight Dan. Wow! I didn't see this as being so complicated. My deadline is Monday so I don't think I will have time to write a dll as you've described. My first thought is to either remove the double quotes from the string being passed in and parse it out with the "ands" for the contains clause, or check if the first and last characters are double quotes and if they are replace them with single quotes and look for the exact phrase. Does that sound too simplified? Thanks again, Phill > The reason it doesn't work when you use 'harley for sale' is that the > char(34) adds double quotes to the string, turning it into an exact phrase [quoted text clipped - 74 lines] > >>> a variable like I am. Does anyone know the syntax? Am I missing > >>> anything else? Thanks. If the first and last are double quotes, indicating a phrase search, then leave them as they are as that is what CONTAINS expects for a phrase match. Also read Hilary's other reply - I only provided the solution of using 'and' as a possible way of doing this. Dan Phill wrote on Thu, 20 Apr 2006 08:41:02 -0700: > Thanks for the insight Dan. Wow! I didn't see this as being so > complicated. My deadline is Monday so I don't think I will have time to [quoted text clipped - 85 lines] >>>>> use a variable like I am. Does anyone know the syntax? Am I missing >>>>> anything else? Thanks. While I remember, here are some links you might want to look at: http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800 http://www.dbtalk.net/microsoft-public-sqlserver-fulltext/fts-search-question-16 5733.html And I know Hilary wrote a parsing system for word/phrase handling, I'm not sure if it's the one in that second link or a more complex one that I've seen before, but it's well worth looking at for dealing with your searches within your procedure. My DLL solution just happens to be the one I've come up with after years of working with searching on the web sites I run, starting back before we used FTS on SQL - the original code was built to generate complex SQL queries to search Access databases from our web site front end, and when we moved to FTS just had some changes made to include that as an option too to save having to rewrite everything from scratch (I'm a lazy developer at heart :P) Dan That is exactly what i was looking for. You guys/girls are great. > While I remember, here are some links you might want to look at: > [quoted text clipped - 14 lines] > > Dan 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 Thanks for the clarification Dan, your name always had me wondering;) SignatureHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Phill wrote on Thu, 20 Apr 2006 11:55:02 -0700: > [quoted text clipped - 5 lines] > > Dan 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 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 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 [quoted text clipped - 4 lines] > 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. SignatureDan 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 > saleek wrote on Thu, 31 Jul 2008 08:22:03 -0700: > [quoted text clipped - 45 lines] > Look at the CONTAINS clause in your Proc - you're using @keyword, not > @holding which you use in the Query Analyzer code. 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 >> 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 [quoted text clipped - 4 lines] >>> 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. varchar(200) is not a magical number. It could be any length. As Daniel has pointed out this is a strict phrase based match. He recommends using a boolean and for each search term. This will work but it could bring back other incorrect hits, for example Car for sale in Harley, ND. So a thesaurus search would probably work better for you, for example use an expansion of harley to harly, and harley davidson. A thesaurus based search would return hits to harley for sale, harley-davidson for sale, but not harley-davidson trikes for sale. SignatureHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Thanks Hilary. It works if I pass in 'for sale', but if I pass in 'harley > for sale' I don't get any results. My data has a record that has "Motor [quoted text clipped - 45 lines] >> > anything >> > else? Thanks. Good point Hilary. I will search BOL for thesaurus search and see how to do this. > varchar(200) is not a magical number. It could be any length. > [quoted text clipped - 55 lines] > >> > anything > >> > else? Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.