SQL 2012 :: FullText Search - Can Search Terms Come From Another Table
Mar 25, 2015
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.
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 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.
basically, what I am trying to achieve to 2 types of search functions...
Search for All terms (easy and complete) and search for Any Terms...
the way I have gone about this so far is to in my asp.net app, split the search string by spaces, and then search for each word, and merging the resulting dataset into the main return dataset.
this, however has a few problems. the result dataset will contain duplicate values, and i am running queries in a loop.
What i am looking for is a one-stop-shop stored procedue that will split the search string, loop through each word, and add the results to a return table, ONLY if it does not exist already within the return table.
Can anyone point me in the right direction... basically with the splitting of the string and the looping through the words...the rest i think i can handle...
or any other hints/tips/tricks would also be helpful.
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
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 have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed --The Path variable is set to the bin folder for the Adobe iFilter. --SQL Developer version 64 bit on both Windows 7 and Windows 8. USE master; GO DROP DATABASE FileTableStudy; GO CREATE DATABASE FileTableStudy ON PRIMARY
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 have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy] SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM INTO T1 FROM (SELECT a.meu, a.mep2, SUM(a.mest) as excst FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch ( Id int not null, AllText nvarchar(400) ) create unique index test_tfts on TestFullTextSearch(Id); create fulltext catalog ftcat_tfts;
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)' AND 'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on... so i thought create site search table, with the columns: id, text, url and to write every thing to this table. but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server... what do you think?
I have Sql server 2005 SP2. I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row. I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row. select * from testtable where contains(description,'"*xting*"') ;
But it works if i do select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search. Is it an issue with sql server 2005?Please help.
I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:
select * from Mytable where contains(MyColumn, '123.345.')
I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:
select * from MyTable where MyCOlumn like '123.345.%';
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
hi there, i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out can anyone help? thanks
