January 30, 2013 at 4:58 pm
I have one table in sql server say example
Table Name candidates
Test table contains some columns
cid , cname,c_resume
cid 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,
February 3, 2013 at 1:42 am
Suresh:
contains give you result which contains your search parameters....use LIKE Instead
OR you can use LEFT as below
WHERE URL = LEFT('')
February 5, 2013 at 12:34 pm
Hi 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.
February 5, 2013 at 1:29 pm
Hi,
please check execution plane....adjust your joins if you have...
otherwise use PATINDEX
February 5, 2013 at 2:05 pm
Hi 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,
February 5, 2013 at 10:46 pm
February 6, 2013 at 3:33 pm
Hi 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,
February 6, 2013 at 6:56 pm
Full-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.
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
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. 😉 ]
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-2 = 0 -- end of the first string unknown
SELECT
@len = LEN(@keywords)
SELECT
@search = ''
SELECT
@fuzzy = ''
WHILE (@j <= @len)
BEGIN
SELECT
@k = @j-2 + 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-2 = 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
+ 'FORMSOF(INFLECTIONAL,"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@k,@len - @k + 1)))
+ '")'
ELSE
SELECT
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
+ 'FORMSOF(INFLECTIONAL,"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
+ '") AND '
SELECT
@i = @j-2 + 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 ON
END
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.
CREATE PROCEDURE [dbo].[FullTextSearchProducts]
@SearchTerm NVARCHAR(4000)
,@MinRelevance INT
,@MaxRows INT
,@SilentExecution BIT = 0
,@NumRows INT = 0 OUTPUT
AS
BEGIN
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.
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 0
END
The final procedure that returns the results:
CREATE PROCEDURE [dbo].[SearchProducts]
@ProdID INT
,@strSearchTerms NVARCHAR(MAX) = NULL
AS
BEGIN
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.ProdID
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply