I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.
It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow: =================== SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
Hi,I'm doing a search function for recipe database and have the query:1 SELECT K.RANK, tRecipe.sHeadline, tRecipe.sIngredients, tRecipe.sImagePath 2 FROM tRecipe 3 INNER JOIN 4 FaktaRecipe ON tRecipe.iRecipeID = FaktaRecipe.iRecipe 5 INNER JOIN 6 CONTAINSTABLE(tRecipe, *, 'ISABOUT (chick* WEIGHT(0.2))') AS K 7 ON tRecipe.iRecipeID = K.[KEY] 8 WHERE (FaktaRecipe.iRecipeFakta = 5) 9 ORDER BY RANK DESC I want to return records like 'chicken pie' etc, hence using the wildcard in chick* BUT the wildcard doesn't work! It works fine if I use the whole word 'chicken' but of course a user won't always do that... I am using SQL server 2000. Any ideas? - I'm tearing my hair out! Thanks,Paul
I'm trying to use Full-Text Search to rank online searches of my products on my site. The user enters 2 keywords for a search. The search results are ordered by descending rank.
Every example I've seen (Microsoft) using ISABOUT hard codes the keywords that are used in the search. My SP works if I do in fact hard code the search terms, but I obviously want the keywords to be variables (@Keyword and @Keyword2).
What is the syntax for using @Keyword in the ISABOUT statement?
SELECT t.Rank, P.* FROM tbProducts P INNER JOIN containstable(tbProducts, ProductName, 'ISABOUT(@Keyword weight (.8), @Keyword2 weight (.7))') t ON P.ProductID = t.[key] ORDER BY t.Rank DESC -------------------------------------------------------
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3 FROM tbl1 t1, tbl2 t2 WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
I'm trying to write a query to associate to an alternate key. Cansomeone provide the right syntax/keyword I need to accomplish this?Here's the line that keeps giving me the error (Operand type clash:uniqueidentifier is incompatible with int):JOIN CONTAINSTABLE(VocabularyFrench, *, 'FORMSOF(INFLECTIONAL,"remorques")') SRCH ON P.PRODUCT_ID = SRCH.[KEY]Thanks,Jeff
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g. megger meg mft megger (proper order in the database is megger mft) mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code: <% Dim RSResults__param5 RSResults__param5 = "xxx" If (Request("searchme") <> "") Then RSResults__param5 = Request("searchme") End If
I'm building a standard search function for this project im working on and I wanted to know how could I do the following; Basically want I am using the standard "LIKE" function to search keywords but I also want to rank the returns based on how many matches. For example if someone uses the keywords "dog run park fast" and I have a couple of records that have certain amount combinations with these words, how would I return the one that has the most combinations to the least. If one record had "dog, run,park", another has "dog run" and the last would have "fast". How would I being those results in that order.Thanks for any help.
I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))
I have then created a full text catalog on that table and populated it.
Then i wrote the following query "select contents from tblcatalog where contains(contents,'sample data')" It is fetching 0 records even though u have 5 records with entries "sample data"
Apologies if this is not the correct place to put this.I wrote this query to perform a fulltext search on tblPages.SELECT * FROM tblPages,FREETEXTTABLE(tblPages, *,@searchTerm) searchTableWHERE [KEY] = tblPages.PageID ORDER BY RANK DESCWhere PageID is the primary key.The synax is correct but why does it never return any results?thanksmarc
i know this is a bit of a shot in the dark but, does anybody know of a reasonably understandable way to rank XML node path results based on keyword search terms? seems like there are tons of uber-complex docs on the internet about this. i dont mind if it is a bit of a work-around, i am just looking for something that is reasonable non-complex that i can wrap my mind around. links etc apprecaited.
Hi, I created the following table in which i created full text indexing for DocDatafld and TypeColumn was used as Type Column for DocDataFld field. I store Document name in the DocumentTitlefld and want to get the Document name and Document and want to display it in the front end application and give url for the document. Documentidfld bigint Documenttitlefld varchar MimeTypefld varchar DocDatafld varbinary(MAX) TypeColumn varchar and i am using the following query to get the contents, but not able to get the column values. select documenttitlefld,docdatafld from Documentdatatbl Freetext(DocDatafld,'C') Please help
I'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.
I am Using a Full Text Search Engine to Search Members of My Site so getting a Problem in Paging Concept I Basically want FullText Search Should Return me the Row he has Found I Dont want to write a new Query to get total resulset Please can anyone Help Me
I had problem when change database from sqlserver 2000 to sql express 2005.The fulltext index does not create automatically.Example: i use sqldatasource to insert new name to my table.and then i find it by query like this : select count(*) from mytable where contains(mycol,'newname')the result is 0but if i run query to start full index : exec sp_fulltext_catalog 'myfulltext','start_full' and run query select count(*) from mytable where contains(mycol,'newname')again. The result is 1. So i alway run exec sp_fulltext_catalog 'myfulltext','start_full' after insert or update, delete to create fulltext index. When i use sql server 2000 , i didn't need do that, it automatic. Pls help me !!! how to make fulltext index create auto in sql express 2005 .
SQL Server 2005: Anyone know how best to rewrite this SQL string to perform a weighted search on my table? here's my code: SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')" the idea is to get a list of recipes most pertinant to the ingredients entered. table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.
at the moment it returns the correct recipes but not in the right order. If I use 'CONTAINS' it returns nothing. hope that's not too vague!
Hello, I try to get the full-text search on SQL Server Express with advanced Services SP2 running, but I got some problems. First I tell you what I did. I created a new database called TestDB.
Then I created a TestTable inside the db with a Primary Key:
sp_fulltext_database enable create fulltext catalog testcatalog as default create fulltext index on TestTable(text) key index PK_TestTable
Now the problem: When I insert some rows in the Table the catalog get not populated. (change Tracking Setting is set to auto) Instead these error messages are written into the crawl log:
Error '0x80004005' occurred during full-text index population for table or indexed view '[TestDB].[dbo].[TestTable]' (table or indexed view ID '2073058421', database ID '5'), full-text key value 0x00000002. Attempt will be made to reindex it.
The component 'sqlfth90.dll' reported error while indexing. Component path 'c:ProgrammeMicrosoft SQL Server90COMsqlfth90.dll'.
I just implemented full text search on a table and it doesn't work like I expected. I am using a stored procedure to search a company name column.
CREATE PROCEDURE swsp_GetCompanyRecord @companyName varchar(120) as begin select recID, companyName from TABLE where contains (companyName, @companyName) end
I use the stored procedure as follows:
swsp_GetCompanyRecord '"Reynolds Aluminum"' (Quotation marks inside single quote)
If I search for a company (e.g., Reynolds Aluminum), I get the following results:
Search Returns Reynolds Reynolds Aluminum Reynolds A Reynolds Aluminum Reynolds Al nothing Reynold nothing Aluminum Reynolds Aluminum
I guess I expected this to work similar to 'LIKE' in that it would return the full company name if I gave it any portion of the name. Is there something wrong with the way I've implemented this?
Hello,I am using SQLServer Express and Visual Studio 2005 to create the website. I would like to implement FullText search, but have never done it before. I have looked at the msdn documentation on FullText search in SQLServer 2005 here http://msdn2.microsoft.com/en-us/library/ms142519.aspx.I cannot seem to figure out how to use a FT search using Visual Studio. Can someone please help me configure my database and then explain how I can run queries based on user input to return data?
I am using SSIS to replace set of tables daily. One of the table has primary, unique, foreign keys and full-text index. Before truncating, I am dropping the foreign key constraints (to truncate the parent table), truncating the tables and recreating the foreign keys.
I have few questions:
1) Do I need to drop and recreate the unique key as well? (I am not dropping the primary key) - Unique key is identity column created just for the full-text indexing since it was mentioned that key on integer is better than key on varchar and my pk is a varchar.
2) Do I need to drop and recreate the full-text index or just rebuild/repopulate it every time the table is loaded.
This is the first time i am using full text index and I was able to learn a lot about it from the sites. I would like to understand the correct approach while loading the tables.
is there a rational explanation for which after some select statements, the rank returned by the full-text search engine is 0, knowing that just after the repopulation the rank is displayed correctly?
in other words, time and usage messes up the ranking. why?
We have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.
We observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
TableFulltextItemCount is around 2.2 crores.
Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.
As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.
the below query gives 2 results that are from actual base table
HTML Code: select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810
To search for the same above word using FTS,I have used the query as below
HTML Code: SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC
I have attached one sql script file for your ref that contains create logic and index schema properties
I am having problems with syntax errors being generated from the search clause of a full-text search. When the syntax error is raised, the batch and transaction are stopped. Placing the search within a SQL Try/Catch allows the batch to continue, but the transaction is uncommitable.
I need to trap and handle the full-text search syntax error then control the commit or rollback of the transaction based on other transactions.
Below is a simplified version of the type of logic I am working with. The XACT_STATE goes to -1 because of the syntax error generated by the full-text search. This forces a rollback of the whole transaction.
Declare @sSearch NVarChar(50) /* @sSearch is passed by a web page and could come back empty or with an invalid Full-Text search clause*/
Begin Transaction Begin Try Print XACT_STATE() Select * /*The table 'KnowledgeBase' is a table with a populated Full-Text Search*/ From Containstable(KnowledgeBase,*,@sSearch) End Try Begin Catch Print XACT_STATE() Print 'Number- ' + Convert(VarChar,ERROR_NUMBER()) + ', ' + 'Severity- ' + Convert(VarChar,ERROR_SEVERITY()) + ', ' + 'State- ' + Convert(VarChar,ERROR_STATE()) + ', ' + 'Message- "' + ERROR_MESSAGE() + '"' End Catch
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S1' THEN 'Other sale items' ELSE 'Not for sale' END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
what use reason of 'weighted-term' ?explain it. SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, 'ISABOUT(mohsen weight(.8),yaser weight(1.0))') table [contain-1] information: ID FIRSTNAME 1 mohsen 2 mohsen 3 yaser 4 mehdi
The below is the asp code pasted here to explain what I am trying to do. How to Sum the weight of the item group by itemcode which is passed as a parameter
Do While Not objRS.EOF
itmcode = objRS("SKU")
sSQL = "SELECT code, numpacks, weight FROM tblproducts WHERE code = '" & itmcode & "'" Set rsNumpks = Conn.Execute(sSQL) If Not rsNumpks.EOF Then numpacks = CDbl(rsNumpks("Numpacks")) weigh = CDbl(rsNumpks("Weight")) End If Set rsNumpks = Nothing Loop
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S2' THEN 'Other sale items' ELSE 'Not for sale' END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
Is there any way to retrieve the weight % of some values?
A quick example: A 10 -> Result i want is 10 / 60 (60 is the SUM of A,B and C) B 20 -> 20 /60 C 30 -> 30/60 Code for number of subscriptions EVALUATE SUMMARIZE('Subscriptions', 'Subscriptions Type'[DS_SUBS_TYPE], "Number of Subscriptions ",'Subscriptions'[NR_SUBSCRIPTIONS]).
Is it possible to dynamically set the font weight (bold vs normal) for a matrix row label? I always get an invalid value message box from the expression editor. For example:
