Boolean and Wildcard search cracked
Carl Lewis
To enable boolean and wildcard searches in your WSS site, replace the
code in the procedure proc_FetchDocSearchResults with the following
code:
CREATE PROCEDURE proc_FetchDocSearchResults(@SearchTerm nvarchar(255),
@SiteId uniqueidentifier, @WebId uniqueidentifier, @RankPaging int,
@ModifiedPaging datetime, @ItemIdPaging uniqueidentifier) AS
DECLARE @i INT, @spos INT, @QuotMode BIT, @tstring VARCHAR(255), @fl
INT
--execute the other procedure if this is a freetext search
IF SubString(@SearchTerm,2,2) = 'FT' BEGIN
SET @SearchTerm = '"' + SubString(@SearchTerm, 2,len(@SearchTerm))
+ '"'
EXECUTE proc_FetchDocSearchResultsFreeText @SearchTerm , @SiteId ,
@WebId , @RankPaging , @ModifiedPaging , @ItemIdPaging
END
ELSE BEGIN
/* Fix the search term */
--Remove any leading and ending quotes and spaces from the Search Term
IF LEFT(@SearchTerm, 1) = CHAR(34) BEGIN
SET @SearchTerm = RIGHT(@SearchTerm, LEN(@SearchTerm) -1 )
END
IF RIGHT(@SearchTerm, 1) = CHAR(34) BEGIN
SET @SearchTerm = LEFT(@SearchTerm, LEN(@SearchTerm) - 1)
END
SET @SearchTerm = RTRIM(LTRIM(@SearchTerm))
--Remove ' ', ' or ', ' and ' from beginning and end of query (for
users who don't know what they are doing)
--**********************************************************************
WHILE CHARINDEX(' ', @SearchTerm) > 0 BEGIN
SET @i = CHARINDEX(' ', @SearchTerm)
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @i-1) +
SUBSTRING(@SearchTerm, @i+1, LEN(@SearchTerm))
END
--if the first phrase is "and" or "or", remove it
WHILE RTRIM(LTRIM(SUBSTRING(@SearchTerm,1,4))) = 'and' or
RTRIM(LTRIM(SUBSTRING(@SearchTerm,1,3))) = 'or' BEGIN
IF RTRIM(LTRIM(SUBSTRING(@SearchTerm,1,4))) = 'and'
SET @SearchTerm = SUBSTRING(@SearchTerm,4, LEN(@SearchTerm))
ELSE
SET @SearchTerm = SUBSTRING(@SearchTerm,3, LEN(@SearchTerm))
SET @SearchTerm = RTRIM(LTRIM(@SearchTerm))
END
--if the last phrase is "and" or "or", remove it
WHILE RTRIM(LTRIM(SUBSTRING(@SearchTerm, LEN(@SearchTerm)-3,
LEN(@SearchTerm)))) = 'and' or RTRIM(LTRIM(SUBSTRING(@SearchTerm,
LEN(@SearchTerm)-2, LEN(@SearchTerm)))) = 'or' BEGIN
IF RTRIM(LTRIM(SUBSTRING(@SearchTerm, LEN(@SearchTerm)-3,
LEN(@SearchTerm)))) = 'and'
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, LEN(@SearchTerm)-4)
ELSE
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, LEN(@SearchTerm)-3)
SET @SearchTerm = RTRIM(LTRIM(@SearchTerm))
END
--add "and" between words that are not phrases (a phrase has "s around
it)
SET @spos = 1
SET @i = 0
WHILE @spos > 0 BEGIN
--find the next occurance of a space ' ' in the searchstring
SET @spos = CHARINDEX(' ', @SearchTerm, @spos)
IF @spos = 0 BREAK -- quit if there are none left
--check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
SET @fl = LEN(@tstring)
SET @tstring = REPLACE(@tstring, '"', '')
IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1
--are we in the middle of a quote? if so don't add the "and"
IF @QuotMode = 1 BEGIN
--move the position to the next character
SET @spos = @spos + 1
END
ELSE BEGIN --else add the " and "
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' AND ' +
SUBSTRING(@SearchTerm, @spos+1, LEN(@SearchTerm))
SET @spos = @spos + 5 --change the position of the index to after
the new " and "
END
END
--eliminate ' and or and '
SET @spos = 1
WHILE @spos > 0 BEGIN
--find the next occurance of a ' and or and ' in the searchstring
SET @spos = CHARINDEX(' and or and ', @SearchTerm, @spos)
IF @spos = 0 BREAK -- quit if there are none left
--check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
SET @fl = LEN(@tstring)
SET @tstring = REPLACE(@tstring, '"', '')
IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1
--are we in the middle of a quote? if so don't fix the occurance
IF @QuotMode = 1 BEGIN
--move the position to the next character
SET @spos = @spos + 12
END
ELSE BEGIN --else fix it
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' or ' +
SUBSTRING(@SearchTerm, @spos+12, LEN(@SearchTerm))
SET @spos = @spos + 4 --change the position of the index to after
the fixed ' or '
END
END
--eliminate ' and and and '
SET @spos = 1
WHILE @spos > 0 BEGIN
--find the next occurance of a ' and and and ' in the searchstring
SET @spos = CHARINDEX(' and and and ', @SearchTerm, @spos)
IF @spos = 0 BREAK -- quit if there are none left
--check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
SET @fl = LEN(@tstring)
SET @tstring = REPLACE(@tstring, '"', '')
IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1
--are we in the middle of a quote? if so don't fix the occurance
IF @QuotMode = 1 BEGIN
--move the position to the next character
SET @spos = @spos + 13
END
ELSE BEGIN --else add the " and "
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' and ' +
SUBSTRING(@SearchTerm, @spos+13, LEN(@SearchTerm))
SET @spos = @spos + 5 --change the position of the index to after
the fixed ' and '
END
END
--eliminate ' and not and '
SET @spos = 1
WHILE @spos > 0 BEGIN
--find the next occurance of a ' and not and ' in the searchstring
SET @spos = CHARINDEX(' and not and ', @SearchTerm, @spos)
IF @spos = 0 BREAK -- quit if there are none left
--check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
SET @fl = LEN(@tstring)
SET @tstring = REPLACE(@tstring, '"', '')
IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1
--are we in the middle of a quote? if so don't fix the occurance
IF @QuotMode = 1 BEGIN
--move the position to the next character
SET @spos = @spos + 13
END
ELSE BEGIN --else add the " and "
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' and not '
+ SUBSTRING(@SearchTerm, @spos+13, LEN(@SearchTerm))
SET @spos = @spos + 5 --change the position of the index to after
the fixed ' and not '
END
END
--find ** and change the word to FORMSOF(INFLECTIONAL, <word>)
--to do the replace we need to reverse the string and then replace
SET @SearchTerm = REVERSE(@SearchTerm)
SET @spos = 1
WHILE @spos > 0 BEGIN
--find the next occurance of a '**' in the searchstring
SET @spos = CHARINDEX('**', @SearchTerm, @spos)
IF @spos = 0 BREAK -- quit if there are none left
--check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
SET @fl = LEN(@tstring)
SET @tstring = REPLACE(@tstring, '"', '')
IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1
--are we in the middle of a quote? if so don't change the **
IF @QuotMode = 1 BEGIN
--move the position to the next character
SET @spos = @spos + 2
END
ELSE BEGIN --else change it
--find the ' ' before the word
SET @i = CHARINDEX(' ', @SearchTerm, @spos)
IF @i = 0 SET @i = LEN(@SearchTerm)+1
SET @tstring = SUBSTRING(@SearchTerm, @spos+2, @i-@spos-2)
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ')' +
@tstring + reverse('FORMSOF(INFLECTIONAL, ') + SUBSTRING(@SearchTerm,
@spos+LEN(@tstring)+2, LEN(@SearchTerm))
END
END
--find * and change the word to "<word>*"
--to do the replace we need to reverse the string and then replace;
already reversed remember
SET @spos = 1
WHILE @spos > 0 BEGIN
--find the next occurance of a '*' in the searchstring
SET @spos = CHARINDEX('*', @SearchTerm, @spos)
IF @spos = 0 BREAK -- quit if there are none left
--check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
SET @fl = LEN(@tstring)
SET @tstring = REPLACE(@tstring, '"', '')
IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1
--are we in the middle of a quote? if so don't change the *
IF @QuotMode = 1 BEGIN
--move the position to the next character
SET @spos = @spos + 1
END
ELSE BEGIN --else change it
--find the ' ' before the word
SET @i = CHARINDEX(' ', @SearchTerm, @spos)
IF @i = 0 SET @i = LEN(@SearchTerm)+1
SET @tstring = SUBSTRING(@SearchTerm, @spos, @i-1)
SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + char(34) +
@tstring + char(34) + SUBSTRING(@SearchTerm, @spos+LEN(@tstring),
LEN(@SearchTerm))
END
END
SET @SearchTerm = REVERSE(@SearchTerm)
/*do the search */
SET NOCOUNT ON
SELECT
NULL,
NULL,
NULL,
Docs.DirName,
Docs.LeafName,
Docs.DocLibRowId,
Docs.ListId,
Lists.tp_Title,
UserData.tp_Author,
UserData.tp_Editor,
UserInfo.tp_Title,
Docs.TimeLastModified AS LastModified,
Docs.[Size],
Docs.Id AS ItemId,
Docs#CT.Rank AS CT#Rank,
CASE WHEN (Docs.Type = 1)
THEN 3
ELSE 2
END
FROM
Docs
INNER JOIN
ContainsTable(Docs, *, @SearchTerm) AS Docs#CT
ON
Docs#CT.[Key] = Docs.ID
LEFT OUTER JOIN
Lists
ON
Docs.ListId = Lists.tp_ID
LEFT OUTER JOIN
UserData
ON
(Docs.ListId = UserData.tp_ListId) AND
(Docs.DocLibRowId = UserData.tp_ID)
LEFT OUTER JOIN
UserInfo
ON
(UserData.tp_Editor = UserInfo.tp_ID) AND
(UserData.tp_SiteId = UserInfo.tp_SiteID)
WHERE
@SiteId = Docs.SiteId AND
@WebId = Docs.WebId AND
((Lists.tp_Flags IS NULL) OR
((Lists.tp_Flags & 0x400) = 0) OR
(UserData.tp_ModerationStatus IS NULL) OR
(UserData.tp_ModerationStatus = 0)) AND
((Docs.Type = 0) OR
((Docs.Type = 1) AND
(Docs.DoclibRowId IS NOT NULL))) AND
((@RankPaging IS NULL) OR
(Docs#CT.Rank < @RankPaging) OR
((Docs#CT.Rank = @RankPaging) AND
((Docs.TimeLastModified < @ModifiedPaging)
OR
((Docs.TimeLastModified = @ModifiedPaging)
AND
(Docs.Id > @ItemIdPaging)))))
ORDER BY
CT#Rank DESC,
LastModified DESC,
ItemId ASC
RETURN 0
END
GO
Mike Walsh
Will someone else try this out on their machine and confirm that it works,
please !
With the Finnish summer just starting (and with it being very very short)
plus Tech-Ed Europe in a week's time. I'm a bit short of testing time at the
moment.
(Carl, can you also send me this as a text file or something else with no
end-of-line problems to englantilainen at mvps dot org - give it a WSS
relevant title so I don't miss it)
Mike Walsh, Helsinki, Finland
WSS FAQ at wss.collutions.com
Please reply to the newsgroup
"Carl Lewis" <cal...@yahoo.co.uk> wrote in message
news:bb94dedd.04061...@posting.google.com...
Carl Lewis
I forgot to mention a few things about this search:
About the short post I made earlier about adding "s to the
searchterm and changing fulltext to containstext:
The results produced by that search were very inconsistent.
The search was also not very intuitive.
So I went to work creating a search function that would
satisfy the following criteria:
1. Look for words with the same prefix. For example, in your
query form type key* to find key, keying, keyhole,
keyboard, and so on.
2. Search for all forms of a word. For example, in the form
type sink** to find sink, sinking, sank, and sunk.
3. Refine your queries with the AND NOT keywords to exclude
certain text from your search. For example, if you want
to find all instances of surfing but not the Net, write the
following query:
surfing AND NOT the Net
4. Add the OR keyword to find all instances of either one word
or another, for example:
Abbott OR Costello
This query finds all pages that mention Abbott or Costello
or both.
5. Put quotation marks around keywords if you want the
Search Engine to take them literally. For instance, if you
type the following query:
"surf the net"
The Search Engine will literally look for the complete
phrase surf the net. But if you type the same query without
the quotation marks:
surf the net
The Search Engine searches all documents for the words
surf and net ("the" is ignored)
6. The standard search will put AND between all your words. If
you want OR's, type "FT <type the search terms>" eg.
FT surf the net
The Search Engine searches all documents for the words
surf or net ("the" is ignored)
Note: 6 provides the search functionality that WSS gives as
standard functionality.
Also:
You need to make an exact copy of the standard stored procedure
proc_FetchDocSearchResults and call it
proc_FetchDocSearchResultsFreeText. This will ensure that
I have found a bug in the first few lines... it should be
CREATE PROCEDURE proc_FetchDocSearchResults(@SearchTerm nvarchar(255),
@SiteId uniqueidentifier, @WebId uniqueidentifier, @RankPaging int,
@ModifiedPaging datetime, @ItemIdPaging uniqueidentifier) AS
DECLARE @i INT, @spos INT, @QuotMode BIT, @tstring VARCHAR(255), @fl
INT
--execute the other procedure if this is a freetext search
IF SubString(@SearchTerm,2,2) = 'FT' BEGIN
SET @SearchTerm = '"' + SubString(@SearchTerm, 5,len(@SearchTerm))
EXECUTE proc_FetchDocSearchResultsFreeText @SearchTerm , @SiteId ,
@WebId , @RankPaging , @ModifiedPaging , @ItemIdPaging
END
ELSE BEGIN
and then everything else is the same.
Saran
This is a fantastic idea of trying to directly modify the stored
procedures , but daring one. When I went through your stored procedure,
I found that one of the statment contains "@i...@spos-2". Could you let
me know what it is ? .
Another questions is that when I tried to check the SQL Server activity
in the Profiles, I was not able to see that the
proc_FetchDocSearchResults SP being used at all. I tried to see all
dependencies of the used SP's but it is not linked to this stored
procedure. I am installed SP1 of WSS.
Will you be able to reply to me at the earliest ?
THanks
D.Saravanan
Mike Walsh
Mike Walsh (Tenerife)
"unknown" wrote:
> Community Message Not Available