|
|
You're in the
sql server full text search
group:How to search for Keywords? Thank You.
sql server full text search:
Hello,
I have full text in a MS SQL database table. I also have a string "Search" which contains all the keywords passed in the URL to the page.aspx. I want to use the keywords. What I have now is this: SELECT * FROM dbo.documents WHERE CONTAINS (*, '"ASP*" or "BOOK*"') Of course this only looks only for 2 well defined words. Can you tell me how can I use the keywords instead? Thanks, Miguel
Miguel,
Could you post the full output of -- SELECT @@version -- as this is very helpful information in not only understanding your environment, but in troubleshooting SQL FTS issues. If I understand your request, you're interested in passing the ASP (.NET?) string "Search" which is the users search input to search your website. Correct? If so, then you want to pass these search keywords into the <search_condition> of the CONTAINS clause. Correct? Then you may be interested in this very good tutorial - "Integrating User Search with ASP and SQL Server Full-Text Search" (By Robert Dominy) at http://www.15seconds.com/issue/010423.htm. If this is not what you're looking for could you provide a more detailed explanation? Thanks, John [quoted text, click to view] "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message news:OeR6uOv3EHA.2876@TK2MSFTNGP12.phx.gbl... > Hello, > > I have full text in a MS SQL database table. > I also have a string "Search" which contains all the keywords passed in > the URL to the page.aspx. I want to use the keywords. > > What I have now is this: > SELECT * > FROM dbo.documents > WHERE CONTAINS (*, '"ASP*" or "BOOK*"') > > Of course this only looks only for 2 well defined words. > Can you tell me how can I use the keywords instead? > > Thanks, > Miguel >
Miguel,
Now, I understand better what you need to know. I believe that this functionality is best achieved in a stored procedure, for example using the Pubs database table pub_info and its text column pr_info: DROP PROCEDURE sp_FTSearchPubsInfo go CREATE PROCEDURE sp_FTSearchPubsInfo ( @vcSearchText varchar(7800)) AS declare @s as varchar (8000) set @s='select pub_id, pr_info from pub_info where contains(*,'+''''+@vcSearchText+''''+')' exec (@s) go Exec sp_FTSearchPubsInfo '"book*"' -- returns: simple use to search for book, books, booking EXEC sp_FTSearchPubsInfo '("book*") or ("publish*")' -- returns: all eight rows in the pub_info table. EXEC sp_FTSearchPubsInfo '(("book" or "books") and ("publish" or "publisher"))' -- returns: only the two rows that have these specific words. You can then use this very flexible approach to pass your search keywords - any number of them, including wildcards (trailing "*" asterisks), and use "(" and ")" to enforce order. A more complex query would look like this: EXEC sp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or "wastepaper") or ("recycle* paper") or (("paper slurry") and ("paper sludge")) or ("biodegrad* paper") or ("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near "chemical*"))' -- returns: 0 rows because none of these words are in the pr_info column of the table pub_info. EXEC sp_FTSearchPubsInfo '("books") or ("testing") and ("for")' -- returns: 2 rows, even though "for" is a noise word in the US_English noise word file noise.enu However, if you change the above query to: EXEC sp_FTSearchPubsInfo '(("books") or ("testing")) and ("for")' it returns the error Msg 7619 - "Execution of a full-text operation failed. A clause of the query contained only ignored words". So, keep in mind that you may want to use some form of either client-side or server-side editing of the search keywords to remove the noise words that are in your language-specific noise word file under \FTDATA\SQLServer\Config. You may also want to remove some of these noise words as well, but not all of them to create an empty (0 length) file as there is a bug with using an empty noise word file. You may also want to review KB article 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at: http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800 Let me know if you find this helpful! Thanks, John [quoted text, click to view] "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message news:#Y25jX33EHA.3908@TK2MSFTNGP12.phx.gbl... > Hi, > > I have a page with a search form where keywords are submitted. > Consider I write the keywords 'asp' and 'book'. > The results page is called as follows: results.aspx?search=asp%20book > > Then I use this script in results.aspx to put the keywords in a string: > > Sub Page_Load(sender As Object, e As System.EventArgs) > Dim keywords() As String = > Request.QueryString("search").Split(CChar("")) > End Sub > > My table is set for FULL TEXT SEARCH. > Consider the SQL when I look for records containing 'asp' and 'book': > > SELECT * > FROM dbo.documents > WHERE CONTAINS (*, 'ASP') AND CONTAINS (*, 'BOOK') > > This SQL looks only for 'ASP' and 'BOOK' words. > I need to look for the Keywords included in the string keywords(). > > If I am not wrong I need to do something like: > WHERE CONTAINS (*, keyword(1)) AND CONTAINS (*, keyword(2))... AND > CONTAINS (*, keyword(i)) > > This is what I don't know how to do. > > Thank You, > Miguel > > "John Kane" <jt-kane@comcast.net> wrote in message > news:jt-kane@comcast.net: > > Miguel, > > Could you post the full output of -- SELECT @@version -- as this is very > > helpful information in not only understanding your environment, but in > > troubleshooting SQL FTS issues. > > > > If I understand your request, you're interested in passing the ASP > > (.NET?) > > string "Search" which is the users search input to search your website. > > Correct? If so, then you want to pass these search keywords into the > > <search_condition> of the CONTAINS clause. Correct? Then you may be > > interested in this very good tutorial - "Integrating User Search with > > ASP > > and SQL Server Full-Text Search" (By Robert Dominy) at > > http://www.15seconds.com/issue/010423.htm. > > > > If this is not what you're looking for could you provide a more detailed > > explanation? > > > > Thanks, > > John > > > > > > > > "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message > > news:OeR6uOv3EHA.2876@TK2MSFTNGP12.phx.gbl... > > > Hello, > > > > > > I have full text in a MS SQL database table. > > > I also have a string "Search" which contains all the keywords passed > > > in > > > the URL to the page.aspx. I want to use the keywords. > > > > > > What I have now is this: > > > SELECT * > > > FROM dbo.documents > > > WHERE CONTAINS (*, '"ASP*" or "BOOK*"') > > > > > > Of course this only looks only for 2 well defined words. > > > Can you tell me how can I use the keywords instead? > > > > > > Thanks, > > > Miguel > > > >
Hi,
I have a page with a search form where keywords are submitted. Consider I write the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book Then I use this script in results.aspx to put the keywords in a string: Sub Page_Load(sender As Object, e As System.EventArgs) Dim keywords() As String = Request.QueryString("search").Split(CChar("")) End Sub My table is set for FULL TEXT SEARCH. Consider the SQL when I look for records containing 'asp' and 'book': SELECT * FROM dbo.documents WHERE CONTAINS (*, 'ASP') AND CONTAINS (*, 'BOOK') This SQL looks only for 'ASP' and 'BOOK' words. I need to look for the Keywords included in the string keywords(). If I am not wrong I need to do something like: WHERE CONTAINS (*, keyword(1)) AND CONTAINS (*, keyword(2))... AND CONTAINS (*, keyword(i)) This is what I don't know how to do. Thank You, Miguel [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message
news:jt-kane@comcast.net: > Miguel, > Could you post the full output of -- SELECT @@version -- as this is very > helpful information in not only understanding your environment, but in > troubleshooting SQL FTS issues. > > If I understand your request, you're interested in passing the ASP > (.NET?) > string "Search" which is the users search input to search your website. > Correct? If so, then you want to pass these search keywords into the > <search_condition> of the CONTAINS clause. Correct? Then you may be > interested in this very good tutorial - "Integrating User Search with > ASP > and SQL Server Full-Text Search" (By Robert Dominy) at > http://www.15seconds.com/issue/010423.htm. > > If this is not what you're looking for could you provide a more detailed > explanation? > > Thanks, > John > > > > "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message > news:OeR6uOv3EHA.2876@TK2MSFTNGP12.phx.gbl... > > Hello, > > > > I have full text in a MS SQL database table. > > I also have a string "Search" which contains all the keywords passed > > in > > the URL to the page.aspx. I want to use the keywords. > > > > What I have now is this: > > SELECT * > > FROM dbo.documents > > WHERE CONTAINS (*, '"ASP*" or "BOOK*"') > > > > Of course this only looks only for 2 well defined words. > > Can you tell me how can I use the keywords instead? > > > > Thanks, > > Miguel > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |