Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Full Text Searching Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 4:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:54 PM
Points: 46, Visits: 252
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,
Post #1413798
Posted Sunday, February 3, 2013 1:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:06 AM
Points: 144, Visits: 261
Suresh:
contains give you result which contains your search parameters....use LIKE Instead

OR you can use LEFT as below
WHERE URL = LEFT('')
Post #1414992
Posted Tuesday, February 5, 2013 12:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:54 PM
Points: 46, Visits: 252
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.
Post #1416035
Posted Tuesday, February 5, 2013 1:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:06 AM
Points: 144, Visits: 261
Hi,
please check execution plane....adjust your joins if you have...
otherwise use PATINDEX
Post #1416064
Posted Tuesday, February 5, 2013 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:54 PM
Points: 46, Visits: 252
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,
Post #1416091
Posted Tuesday, February 5, 2013 10:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:06 AM
Points: 144, Visits: 261
Suresh:

Please follow these links
http://msdn.microsoft.com/en-us/library/ms187787.aspx

AND

http://stackoverflow.com/questions/9800117/sql-function-contains-does-not-return-expected-results
Post #1416244
Posted Wednesday, February 6, 2013 3:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:54 PM
Points: 46, Visits: 252
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,
Post #1416755
Posted Wednesday, February 6, 2013 6:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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 = 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 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.[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 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


Post #1416783
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse