November 28, 2012 at 4:09 am
we are testing a new project and are trying to use Full Text search.
We have a front end app that allows customer service staff to search for customers. They have the ability to search up to 10 different fields of data. (I.e. First name, Last name, Address, etc)
We setup a Full Text index on the columns possible.
Now we are having trouble getting the query to return the correct results.
Several attempts have been made using freetext and contains and results will not return.
For FREETEXT with multiple columns listed and then passing in a concatenated variable of all input values appears to return an OR result set and we cannot see how to force an AND. Example we pass in a first and last name we get all records with the first name or the last name. So if John Smith is entered we get back all Johns and all Smiths. We want just John Smiths returned.
We tried using CONTAINS in the WHERE clause with all 10 fields. The query does not return good results if any of the 10 fields are blank or NULL. We tried putting an ISNULL and it did not work.
Our conclusion is we might have to build a SP that builds the WHERE clause dynamically and puts the CONTAINS for only input fields that have data.
If anyone has any other suggestions I would like to hear them.
Thanks
gb
November 28, 2012 at 5:29 am
I don't use FULL TEXT, but this isn't the first time I've heard of this sort of issue. Could you post some DDL, sample data, and the querying code you've tested so we can better assist you in resolving this issue?
November 28, 2012 at 5:58 am
------------------------------------------------------
-- FULL TEXT QUERY FREETEXT
------------------------------------------------------
--from gs_Subscriber where freetext((FirstName,LastName,GSCustomerNumber,Phone), '"%Juan Gomez%"')
declare
@FirstName nvarchar(25) = NULL,
@LastName nvarchar(25) = NULL,
@Email nvarchar(256) = NULL,
@Phone nvarchar(25) = NULL,
@Address1 nvarchar(30) = NULL,
@City nvarchar(20) = NULL,
@ZipCode nvarchar(20) = NULL,
@CardNumber nvarchar(20) = NULL,
@GSCustomerNumber nvarchar(20) = NULL
set @FirstName = 'juan'
set @LastName = 'gomez'
set @Email = null
set @Phone = null
set @Address1 = '3222 W AGUSTA BLVD'
set @City = null
set @ZipCode = null
set @CardNumber = null
set @GSCustomerNumber = null
DECLARE @SubscriberSearch VARCHAR(1000)
SET @SubscriberSearch = @FirstName +' '+ @LastName +' '+ @Phone +' '+ @GSCustomerNumber
;WITH cte AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY s.SubscriberID ORDER BY s.SubscriberID, ea.IsActive DESC, a.IsActive DESC) AS RowNum,
s.SubscriberID, s.FirstName, s.LastName,
ea.EmailAddress, ea.IsActive as EmailIsActive,
s.Phone,
a.Address1, a.City, a.ZipCode, a.IsActive as AddressIsActive,
sc.CardNumber, s.GSCustomerNumber
FROM gs_Subscriber s
LEFT JOIN gs_EmailAddress ea ON s.SubscriberID = ea.SubscriberID
LEFT JOIN gs_Address a ON s.SubscriberID = a.SubscriberID
LEFT JOIN gs_Subscription sc ON s.SubscriberID = sc.SubscriberID
WHERE freetext((s.FirstName,s.LastName,s.GSCustomerNumber,s.Phone), @SubscriberSearch)
--AND contains(s.LastName, @LastName)
--AND contains(s.Phone, @Phone)
--AND contains(ea.EmailAddress, @Email)
--AND contains(a.Address1, @Address1)
--AND contains(a.City, @City)
--AND contains(a.ZipCode, @ZipCode)
--AND contains(sc.CardNumber, @CardNumber)
--AND contains(s.GSCustomerNumber, @GSCustomerNumber)
)
SELECT SubscriberID, FirstName, LastName,
EmailAddress, EmailIsActive, Phone,
Address1, City, ZipCode, AddressIsActive,
CardNumber, GSCustomerNumber
FROM cte
WHERE RowNum = 1
ORDER BY SubscriberID, EmailIsActive, AddressIsActive
November 28, 2012 at 6:03 am
We need your table DDL (CREATE TABLE statements) and some sample data to test with, please.
November 29, 2012 at 6:33 pm
This pattern works well in these cases:
...and (@firstname is null or contains(firstname, @firstname))
...
Option (recompile)
November 30, 2012 at 10:06 am
Here's some code I've used. I can't take full credit for the code and apologies to whoever wrote it originally. I've had it in my "toolbox" a long time.
First the parser:
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
@tempstring NVARCHAR(4000)
,@ParsedSearchString NVARCHAR(4000)
EXEC dbo.FullTextParseSearchString
'Red Car'
,'NEAR'
,@tempstring OUTPUT
SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')'
SELECT @ParsedSearchString AS ParsedResult
*/
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)))
+ '")'
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
Now the actual search:
CREATE PROCEDURE [dbo].[FullTextSearch]
@SearchTerm NVARCHAR(4000)
,@MinRelevance INT
,@MaxRows INT
,@NumRows INT = 0 OUTPUT
AS
BEGIN
/* Requires procedure FullTextParseSearchString to parse the search term(s) */
SET NOCOUNT ON
SET @SearchTerm = ISNULL(@SearchTerm,'')
SET @MinRelevance = ISNULL(@MinRelevance,10)
SET @MaxRows = ISNULL(@MaxRows,50)
DECLARE
@proximity VARCHAR(10)
,@tempstring NVARCHAR(4000)
,@ParsedSearchString NVARCHAR(4000)
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
--for testing
--SELECT @proximity
--SELECT @ParsedSearchString
SET ROWCOUNT @MaxRows
SELECT
ROW_NUMBER() OVER (ORDER BY k.[RANK] DESC,a.PublishDate,a.Title) AS RowNum
,a.ArticleID
,a.Title
,a.BodyContent
,a.Author
,a.PublishDate
,k.[RANK] AS Relevance
FROM
dbo.Articles AS a
INNER JOIN
CONTAINSTABLE
(
dbo.Articles
,(*)
,@ParsedSearchString
)
AS k
ON a.ArticleID = k.
WHERE
k.[RANK] > @MinRelevance
AND a.isActive = 1
ORDER BY
k.[RANK] DESC
,a.PublishDate
,a.Title
SET ROWCOUNT 0
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply