InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 06:08:47 GMT20http://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxFull-text search is great but you must change your thinking a bit. It is a ranking system based on proximity (how close together are the search terms) and inflection (grammatical variations such as turn, turning, turned, turns). The end-result is a relevancy score and the results can be ordered by relevance or limited to a score range and ordered by whatever other criteria you might want to apply.Toss out "LIKE" forever. SO...there are three (big) steps.1. First you need to parse the search string. There are many parsers available if you do a search just right here on SQLServerCentral. I include one below written by someone else I picked up somewhere long ago (I apologize that I don't remember the contributor's name). The output of this procedure is a formatted WHERE clause ready to add to the end of your final query.2. Second, you need a procedure for applying the parsed search results against your target table. I like to improve the search by creating a targeted view with titles, keywords, etc., in addition to the primary target table. (In my examples below I'm searching a product table.)3. Finally, you need a query that applies the search results (products in this case). You could certainly combine all of these elements into one big procedure. But I use the first two parts enough that it's worthwhile to have them as separate routines. This is really a minimal and simplified version that doesn't cover every possibility like quoted phrases, required terms (+) or negation (-) or NOT. You can probably find a parser that does that or if you look at the code you might find that it isn't too hard to make the modifications yourself.Once you create the first parsing procedure you can play with it by using this code in a query window (mostly pulled from my second procedure). The output is a properly formatted WHERE clause to work with the full-text index.[code="sql"]DECLARE @SearchTerm VARCHAR(255) ,@proximity VARCHAR(10) ,@tempstring NVARCHAR(4000) ,@ParsedSearchString NVARCHAR(4000)--some example usage SET @SearchTerm = 'Find the best holiday cruise'--SET @SearchTerm = 'Fun AND cruise'--SET @SearchTerm = 'Fun OR cruise'--SET @SearchTerm = 'Fun OR Pacific Cruise'--SET @SearchTerm = 'C#, C++' --you'll have to modify the replace below to allow the '+' signs SET @SearchTerm = REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm,'%',''),'"',''),'+',''),'&','')SET @ParsedSearchString = '1=1 'IF CHARINDEX(' OR ',@SearchTerm) > 0 SET @proximity = 'OR'ELSE IF CHARINDEX(' AND ',@SearchTerm) > 0 SET @proximity = 'AND'ELSE SET @proximity = 'NEAR'IF LTRIM(RTRIM(@SearchTerm)) <> '' BEGIN EXEC dbo.FullTextParseSearchString @SearchTerm ,@proximity ,@tempstring OUTPUT SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')' END SELECT @ParsedSearchString[/code]Now for the actual procedures:First the parsing routine (NOT original to me--thanks to the unknown author).[Edit: I just realized this routine has a WHILE loop in it! I know someone will want to point that out. Now I've been using this particular parsing routine for years and maybe it's time to fix that. But it only loops through half a dozen words or fewer so to the purists I say if it ain't broke, don't fix it. ;-) ][code="sql"]CREATE PROCEDURE [dbo].[FullTextParseSearchString] ( @keywords VARCHAR(500) = NULL ,@proximity VARCHAR(10) = 'NEAR' -- and, or, near ,@parsedstring VARCHAR(500) OUTPUT )AS BEGIN SET CONCAT_NULL_YIELDS_NULL OFF SET NOCOUNT ON DECLARE @sql VARCHAR(2000) ,@where VARCHAR(1000) ,@i INT -- old posn in string ,@j INT -- match posn in string ,@k INT -- new start posn ,@len INT -- string length ,@search VARCHAR(500) ,@search2 VARCHAR(500) ,@fuzzy VARCHAR(1000) ,@keyword VARCHAR(500) ,@synonyms VARCHAR(255) ,@new_keywords VARCHAR(1000) ,@found INT SET @keywords = LTRIM(RTRIM(@keywords)) SET @keywords = REPLACE(@keywords,'*',' ') SET @keywords = REPLACE(@keywords,' AND ',' ') SET @keywords = REPLACE(@keywords,' OR ',' ') SET @keywords = REPLACE(@keywords,'"',' ') SET @keywords = REPLACE(@keywords,'(',' ') SET @keywords = REPLACE(@keywords,')',' ') SET @keywords = REPLACE(@keywords,'o''','') -- mcdonald's ==> mcdonald''s, escape out the ' which is a valid piece of punctuation SET @keywords = REPLACE(@keywords,'''','''''') -- fix é type characters SET @found = CHARINDEX('é',@keywords) IF @found > 0 IF @found = LEN(@keywords) SET @keywords = REPLACE(@keywords,'é','') ELSE SET @keywords = REPLACE(@keywords,'é ','* ') -- set proximity type IF @proximity IS NULL OR @proximity = '' SET @proximity = 'NEAR' SELECT @i = 1 -- start of the first string SELECT @j = 0 -- end of the first string unknown SELECT @len = LEN(@keywords) SELECT @search = '' SELECT @fuzzy = '' WHILE (@j <= @len) BEGIN SELECT @k = @j + 1 -- New start posn -- cut up the string into its individual words, split this -- into its inflectional part and standard FTI search portion SELECT @j = CHARINDEX(' ',@keywords,@k) -- Finish if no more found IF (@j <= 0) BEGIN -- Add the last part of the string SELECT @search = @search + '"' + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1))) + '*"' SELECT @search2 = @search2 + '"' + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1))) + '"' IF NOT SUBSTRING(@keywords,@k,@len - @k + 1) IN ('of','and','at','a','the','&','it','for') SELECT @fuzzy = @fuzzy + 'FORMSOF(INFLECTIONAL,"' + LTRIM(RTRIM(SUBSTRING(@keywords,@k,@len - @k + 1))) + '")' ELSE SELECT @fuzzy = @fuzzy + '1=1' BREAK END IF (@j < @len) BEGIN IF (SUBSTRING(@keywords,@j - 1,1) <> ' ') BEGIN SELECT @search = @search + '"' + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i))) + '*" ' + @proximity + ' ' SELECT @search2 = @search2 + '"' + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i))) + '" ' + @proximity + ' ' IF NOT SUBSTRING(@keywords,@i,@j - @i) IN ('of','and','at','a','the','&','it','for') SELECT @fuzzy = @fuzzy + 'FORMSOF(INFLECTIONAL,"' + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i))) + '") AND ' SELECT @i = @j + 1 END END END IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 2,3) = ' OR' SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 2) IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 3,4) = ' AND' SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 3) -- Example: ("mcdonalds") OR ("mcdonalds*") OR (FORMSOF(INFLECTIONAL,"mcdonalds")) SELECT @search = '"' + @keywords + '" OR ' + '(' + @search2 + ') OR (' + @search + ') OR (' + @fuzzy + ')' SET @parsedstring = @search SET CONCAT_NULL_YIELDS_NULL ONEND[/code]Then do the search. NOTE that about halfway down there is a replace to get rid of special characters. In your specific case you'd want to remove the '+' sign from the replace since that would be a valid searchable character.[code="sql"]CREATE PROCEDURE [dbo].[FullTextSearchProducts] @SearchTerm NVARCHAR(4000) ,@MinRelevance INT ,@MaxRows INT ,@SilentExecution BIT = 0 ,@NumRows INT = 0 OUTPUTASBEGIN SET NOCOUNT ON SET @SearchTerm = ISNULL(@SearchTerm,'') SET @MinRelevance = ISNULL(@MinRelevance,10) SET @MaxRows = ISNULL(@MaxRows,500) DECLARE @proximity VARCHAR(10) ,@tempstring NVARCHAR(4000) ,@ParsedSearchString NVARCHAR(4000) --add or delete any special characters as required SET @SearchTerm = REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm,'%',''),'"',''),'+',''),'&','') SET @ParsedSearchString = '1=1 ' IF CHARINDEX(' OR ',@SearchTerm) > 0 SET @proximity = 'OR' ELSE IF CHARINDEX(' AND ',@SearchTerm) > 0 SET @proximity = 'AND' ELSE SET @proximity = 'NEAR' IF LTRIM(RTRIM(@SearchTerm)) <> '' BEGIN EXEC dbo.FullTextParseSearchString @SearchTerm ,@proximity ,@tempstring OUTPUT SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')' END IF OBJECT_ID('tempdb..#TempList') IS NOT NULL DROP TABLE #TempList SET ROWCOUNT @MaxRows SELECT DISTINCT p.ProdID ,p.ProdTitle ,p.ProdDesc ,p.ProdKeywords ,pr.[RANK] AS ProductRelevance INTO #TempList FROM dbo.vw_ProductSearch AS p INNER JOIN CONTAINSTABLE ( dbo.Product ,(*) ,@ParsedSearchString ) AS pr ON p.ProdID = pr.[KEY] WHERE 1=1 AND pr.[RANK] > @MinRelevance ORDER BY pr.[RANK] DESC ,p.ProdTitle IF @SilentExecution = 0 BEGIN SELECT DISTINCT ProdID ,ProdTitle ,ProductRelevance FROM #TempList ORDER BY ProductRelevance DESC ,ProdTitle SET @NumRows = @@ROWCOUNT END ELSE BEGIN SELECT @NumRows = COUNT(DISTINCT ProdID) FROM #TempList END SET ROWCOUNT 0END[/code]The final procedure that returns the results:[code="sql"]CREATE PROCEDURE [dbo].[SearchProducts] @ProdID INT ,@strSearchTerms NVARCHAR(MAX) = NULL ASBEGIN SET NOCOUNT ON DECLARE @isSearch BIT IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL DROP TABLE #SearchResults CREATE TABLE #SearchResults ( [ID] INT IDENTITY(1,1) NOT NULL, [ProdID] INT NULL, [ProdTitle] NVARCHAR(255) NULL, [ProductRelevance] INT NULL, PRIMARY KEY (ID)) SET @strSearchTerms = NULLIF(@strSearchTerms,'') IF @strSearchTerms IS NOT NULL SET @isSearch = 1 ELSE SET @isSearch = 0 IF @isSearch = 1 BEGIN /* Get the ProdIDs from the full-text search result */ INSERT INTO #SearchResults EXEC dbo.FullTextSearchProducts @strSearchTerms, 0, 0, 0 END SELECT DISTINCT p.ProdID ,p.ProdTitle ,p.ProdDesc FROM dbo.Product AS p WHERE 1=1 AND ((@isSearch = 1 AND p.ProdID IN ( SELECT ProdID FROM #SearchResults WHERE id > 0 ) ) OR (@isSearch = 0)) ORDER BY p.ProdIDEND[/code]Wed, 06 Feb 2013 18:56:15 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxHi Mathew,Thanks for you reply,But one thing i am not undestanding below query, can you explain why the result id different?select * from candidates WHERE contains(c_resume_text,'C++') -It shwoing c++ and also other than c++ results.select * from candidates WHERE contains(c_resume_text,'C++11') -It showing results.select * from candidates WHERE contains(c_resume_text,'C++1') -It showing Zero results.The 1st query showing c++ results and other than c++ results.What is the Difference between 2nd and 3rd query, i am not understanding...Just i removed one from 2nd query it not showing any result.if there is two ones after c++ it showing result.what is happening?can you explain.Thanks,Wed, 06 Feb 2013 15:33:28 GMTsuresh0534http://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxSuresh:Please follow these linkshttp://msdn.microsoft.com/en-us/library/ms187787.aspxANDhttp://stackoverflow.com/questions/9800117/sql-function-contains-does-not-return-expected-resultsTue, 05 Feb 2013 22:46:06 GMTMethewhttp://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxHi mathew,thanks for your reply,There are no joins in query, just a select statement.I used with PatIndex it's taking so much time to find out the results.Thanks,Tue, 05 Feb 2013 14:05:49 GMTsuresh0534http://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxHi,please check execution plane....adjust your joins if you have...otherwise use PATINDEXTue, 05 Feb 2013 13:29:28 GMTMethewhttp://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxHi Mathew,If i use like condition , it will take so much time to serach.That's why i used the Contains condtion to search.it gives with in fraction of seconds result.If we use like condtion , if the data is large then it will take so much time.So how can i sove that above problem.Thanks.Tue, 05 Feb 2013 12:34:17 GMTsuresh0534http://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxSuresh:contains give you result which contains your search parameters....use LIKE InsteadOR you can use LEFT as belowWHERE URL = LEFT('')Sun, 03 Feb 2013 01:42:19 GMTMethewhttp://www.sqlservercentral.com/Forums/Topic1413798-338-1.aspxI have one table in sql server say example Table Name candidates Test table contains some columnscid , cname,c_resumecid in Int ,cname is nvarchar(50), c_resume is text Data Type.I am Storing candidates resume in c_resume text filed.I have 10000 rows in table.If i search c++ resume it will showing the result of c++ and also it showing the resume other than c++if i search c++11 also it giving the results.if i search c++1 it not showing any results(Zero Results).I have written Query like this:select * from candidates WHERE contains(c_resume_text,'C++') -It shwoing c++ and also other than c++ results.select * from candidates WHERE contains(c_resume_text,'C++11') -It showing results.select * from candidates WHERE contains(c_resume_text,'C++1') -It showing Zero results.The 1st query showing c++ results and other than c++ results.What is the Difference between 2nd and 3rd query, i am not understanding...Please help me,How can i sove the problem,Thanks,Wed, 30 Jan 2013 16:58:20 GMTsuresh0534