May 29, 2009 at 9:15 pm
A while back, i think in March, i posted a question on how to search for any word in any order and using a partial match, full match or exact phrase. The solution suggested was a combination of charindex and replace functions on the string. This solution worked, but it took a long time to run. Ok, so fast forward a couple months. I finally got around to trying out FTI and am using a routine to squish extra spaces out of a user generated search phrase and return either a phrase with ANDs or ORs between words.
E.g., WHERE CONTAINS(description,'civil AND war') .
WHERE CONTAINS(description,'civil OR war')
However...errrrrrr....I am finding that i cannot perform a search for partial words using FTI.
E.g., WHERE CONTAINS(description,'civi')
Do i need to go back to the table scan method for partial searches and use the FTI for full word searches? Or has some genius out there figured a way around this?
Be gentle, I am still just a grasshopper 😀 . Thanks
May 30, 2009 at 2:27 am
You can use wildcard characters in CONTAINS predicate. For e.g.
WHERE CONTAINS( description, '"civi*"' )
--Ramesh
May 30, 2009 at 6:28 am
THanks...it was late last night and I didn't have the 'what if' part of my brain working.
Unfortunately, I will forget all of this in a couple of hours so for everyone's benefit including me, here is the logic:
---------------------------------------------------------------
Here is the final subroutine then, for anyone else looking for a combination of ALL or ANY partial or full word search using FTI:
@SearchTerm you pass in; e.g.: 'civil war' or ' civil wa' or
'war ci'
@searchtype is:
1 for all words and word fragments: e.g., CONTAINS(description,'"*civi*" AND "*wa*"')
2 for all words and no word fragments: e.g., CONTAINS(description,'war AND civil')
3 for any word and any word fragments: e.g., CONTAINS(description,'"*wa*" OR "*ci*"')
4 for any word and no word fragments: e.g., CONTAINS(description,'war OR civil')
@SearchTermRet is the resultant return string for which you can go on to apply to any FTI indexed field.
I have 3 tables with varchar and text fields: name and descrptions where I use with the CONTAINS clause.
This is much quicker than using CHARINDEX or LIKE and I am now an FTI convert.
ALTER PROCEDURE [dbo].[aspdnsf_CP0023_buildSearchPhrase]
@SearchTerm varchar(255)
,@SearchTermRet varchar(255) OUTPUT
,@searchtype int = 1
AS
BEGIN
SET NOCOUNT ON
SET @SearchTermRet = REPLACE(REPLACE(REPLACE(@SearchTerm,' ',''),'><',''),'',' ')
SET @SearchTermRet = LTRIM(@SearchTermRet)
SET @SearchTermRet = RTRIM(@SearchTermRet)
IF(@searchtype = 1) --ALL word search AND word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ','*" AND "*')
SELECT @SearchTermRet = '"*' + @SearchTermRet + '*"'
SELECT @SearchTermRet = '' + @SearchTermRet + ''
END ELSE IF(@searchtype = 2) --ALL word search BUT NOT word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ',' AND ')
SELECT @SearchTermRet = '' + SPACE(1) + @SearchTermRet + SPACE(1) + ''
END ELSE IF(@searchtype = 3) -- ANY word AND word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ','*" OR "*')
SELECT @SearchTermRet = '"*' + @SearchTermRet + '*"'
SELECT @SearchTermRet = '' + @SearchTermRet + ''
END ELSE IF(@searchtype = 4) -- ANY word BUT NO word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ',' OR ')
SELECT @SearchTermRet = '' + SPACE(1) + @SearchTermRet + SPACE(1) + ''
END
END
May 30, 2009 at 7:19 am
brd123 (5/30/2009)
THanks...it was late last night and I didn't have the 'what if' part of my brain working.
Appreciate for the feedback.
--Ramesh
May 30, 2009 at 9:04 am
The wildcard doesn't work at the beginning of search term, i.e. "*ci*" will not match "uncival".
Please correct this if I'm wrong!
May 30, 2009 at 10:45 am
You appear to be correct. ANyone know a work around for this? Or is this a limitation?
May 31, 2009 at 2:40 am
Allister Reid (5/30/2009)
The wildcard doesn't work at the beginning of search term, i.e. "*ci*" will not match "uncival".Please correct this if I'm wrong!
Indeed, it does not work when prefixing wildcards when using prefix_term option of FT functions.
--Ramesh
May 31, 2009 at 8:12 am
Here is the corrected version then. The previous worked but I assumed you could prefix with the wildcard char as well as suffix.
This version works fine for me, hopefully someone else will benefit. And maybe MicroSoft can add this capability to FTI in the future? Lil ole me can't be the only one who has ever bumped into this limitation. I have high hopes for this technology. How do other database products do this? Teradata for example? Their trademark is working with really big databases.
Still, this is a much better solution--speed wise--than using table scans. I am an FTI convert now.
ALTER PROCEDURE [dbo].[aspdnsf_CP0023_buildSearchPhrase]
@SearchTerm varchar(255)
,@SearchTermRet varchar(255) OUTPUT
,@searchtype int = 1
AS
BEGIN
SET NOCOUNT ON
SET @SearchTermRet = REPLACE(REPLACE(REPLACE(@SearchTerm,' ',''),'><',''),'',' ')
SET @SearchTermRet = LTRIM(@SearchTermRet)
SET @SearchTermRet = RTRIM(@SearchTermRet)
SET @SearchTermRet = @SearchTerm
IF(@searchtype = 1) --ALL word search AND begins with, word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ','*" AND "')
SELECT @SearchTermRet = '"' + @SearchTermRet + '*"'
SELECT @SearchTermRet = '' + @SearchTermRet + ''
END ELSE IF(@searchtype = 2) --ALL word search BUT NOT word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ',' AND ')
SELECT @SearchTermRet = '' + SPACE(1) + @SearchTermRet + SPACE(1) + ''
END ELSE IF(@searchtype = 3) -- ANY word AND begins with, word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ','*" OR "')
SELECT @SearchTermRet = '"' + @SearchTermRet + '*"'
SELECT @SearchTermRet = '' + @SearchTermRet + ''
END ELSE IF(@searchtype = 4) -- ANY word BUT NO word fragments
BEGIN
SELECT @SearchTermRet = REPLACE(@SearchTermRet,' ',' OR ')
SELECT @SearchTermRet = '' + SPACE(1) + @SearchTermRet + SPACE(1) + ''
END
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply