 | I | 07.09 - How do I use Boolean functions in my searches? | Derek Fakehany very kindly send me a lot of detail on this.
He praised in his note to me the fix that Carl Lewis had posted in the WSS newsgroup which made him realise that Boolean searches were possible.
The solution below is however a) put in the kind of detailed way that a FAQ item needs and b) includes Boolean searches of Lists which Carl's solution didn't.
Here are Derek's steps -
1. Get into your Windows Sharepoint Services (WSS) box
2. Fire up SQL Enterprise Manager
3. Work your way down to your database(s) for Sharepoint
4. Open your database and then open the Stored Procedures for the database
5. To enable Boolean for document libraries, look for the stored procedure called “proc_FetchDocSearchResults”
6. Make a backup of the procedure (I just right-clicked, selected copy and pasted it into a Notepad document).
7. Right-click on the stored procedure and select “Properties”
8. You’ll get a window with the procedure outlined. Make the following changes:
a. Add the following lines to the procedure before the line "SET NOCOUNT ON":
DECLARE @CustSearchTerm nvarchar(255)
SET @CustSearchTerm = '"' + @SearchTerm + '"'
b. Find the word “FreeTextTable” and replace it with “ContainsTable”
9. Hit Apply & OK
10. To enable Boolean for lists, look for the stored procedure called “proc_FetchListItemSearchResults”
11. Make a backup of the procedure
12. Right-click on the stored procedure and select “Properties”
13. Make the same changes in Step #8 above – HOWEVER, note that there are MULTIPLE OCCURRENCES (three to be exact) of FreeTextTable and @SearchTerm. You need to change ALL THREE OCCURRENCES.
14. Hit Apply & OK.
15. Repeat for each Sharepoint database you may have
16. Close the Enterprise Manager
17. Run IISRESET
Note that the new search only supports and/or but that's all I really need. And it will ONLY work if you actually specify and/or in your search (i.e. “testing AND tips” rather than “testing tips”).
Thanks, Derek! (and thanks too to Carl for starting things moving.
----------------
Billy Brackeen sent me what he described as an enhanced solution using your method.
Note however the next section (a long way down) which has what the writer of that describes as an improved version of this.
Basically I created two alternative scripts (proc_FetchDocSearchResultsBoolean and proc_FetchListItemSearchResultsBoolean).
If the search text contains an ‘ AND ‘ or an ‘ OR ‘ the search is sent from the original code to the new Boolean counterparts. Thus leaving the search behavior the same until a Boolean is entered.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchDocSearchResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchDocSearchResults]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchDocSearchResultsBoolean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchDocSearchResultsBoolean]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchListItemSearchResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchListItemSearchResults]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchListItemSearchResultsBoolean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchListItemSearchResultsBoolean]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE proc_FetchDocSearchResults
(
@SearchTerm nvarchar(255),
@SiteId uniqueidentifier,
@WebId uniqueidentifier,
@RankPaging int,
@ModifiedPaging datetime,
@ItemIdPaging uniqueidentifier)
AS
IF CHARINDEX(' OR ', @SearchTerm) > 0 OR CHARINDEX(' AND ', @SearchTerm) > 0
Begin
EXEC proc_FetchDocSearchResultsBoolean @SearchTerm, @SiteId, @WebId, @RankPaging, @ModifiedPaging, @ItemIdPaging
RETURN
End
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
FreeTextTable(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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE proc_FetchDocSearchResultsBoolean
(
@SearchTerm nvarchar(255),
@SiteId uniqueidentifier,
@WebId uniqueidentifier,
@RankPaging int,
@ModifiedPaging datetime,
@ItemIdPaging uniqueidentifier)
AS
DECLARE @CustSearchTerm nvarchar(255)
SET @CustSearchTerm = '"' + @SearchTerm + '"'
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, *, @CustSearchTerm) 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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE proc_FetchListItemSearchResults
(
@SearchTerm nvarchar(255),
@SiteId uniqueidentifier,
@WebId uniqueidentifier,
@RankPaging int,
@ModifiedPaging datetime,
@ItemIdPaging uniqueidentifier)
AS
IF CHARINDEX(' OR ', @SearchTerm) > 0 OR CHARINDEX(' AND ', @SearchTerm) > 0
Begin
EXEC proc_FetchListItemSearchResultsBoolean @SearchTerm, @SiteId, @WebId, @RankPaging, @ModifiedPaging, @ItemIdPaging
RETURN
End
SET NOCOUNT ON
SELECT
UserData.nvarchar1,
UserData.nvarchar3,
UserData.nvarchar7,
Docs.DirName,
Docs.LeafName,
UserData.tp_ID,
UserData.tp_ListId,
Lists.tp_Title,
UserData.tp_Author,
UserData.tp_Editor,
UserInfo.tp_Title,
UserData.tp_Modified AS LastModified,
NULL,
UserData.tp_GUID AS ItemId,
ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)
AS CT#Rank,
0
FROM
UserData
LEFT OUTER JOIN
FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT
ON
UserData#CT.[Key] = UserData.tp_GUID
LEFT OUTER JOIN
Lists
ON
(Lists.tp_WebId = @WebId) AND
(Lists.tp_ID = UserData.tp_ListId)
LEFT OUTER JOIN
UserInfo
ON
(UserInfo.tp_ID = UserData.tp_Editor) AND
(UserInfo.tp_SiteID = UserData.tp_SiteId)
LEFT OUTER JOIN
Docs
ON
(Docs.SiteId = @SiteId) AND
(Docs.ListId = UserData.tp_ListId) AND
(Docs.DocLibRowId = UserData.tp_ID)
LEFT OUTER JOIN
FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT
ON
UserInfo#CT.[Key] = UserInfo.tp_GUID
WHERE
(@WebId = Lists.tp_WebId) AND
((Lists.tp_Flags IS NULL) OR
((Lists.tp_Flags & 0x400) = 0) OR
(UserData.tp_ModerationStatus = 0)) AND
((UserData#CT.Rank IS NOT NULL) OR
(UserInfo#CT.Rank IS NOT NULL)) AND
((@RankPaging IS NULL) OR
(ISNULL(UserData#CT.Rank, 0) +
ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR
((ISNULL(UserData#CT.Rank, 0) +
ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND
((UserData.tp_Modified < @ModifiedPaging) OR
((UserData.tp_Modified = @ModifiedPaging) AND
(UserData.tp_GUID > @ItemIdPaging)))))
UNION ALL
SELECT
Lists.tp_Title,
NULL,
NULL,
Docs.DirName,
Docs.LeafName,
NULL,
Lists.tp_ID,
NULL,
NULL,
NULL,
NULL,
Lists.tp_Modified AS LastModified,
NULL,
Lists.tp_ID AS ItemId,
Lists#CT.Rank AS CT#Rank,
1
FROM
Lists
INNER JOIN
FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT
ON
Lists#CT.[Key] = Lists.tp_ID
LEFT OUTER JOIN
Docs
ON
Lists.tp_RootFolder = Docs.[Id]
WHERE
(@WebId = Lists.tp_WebId) AND
((@RankPaging IS NULL) OR
(Lists#CT.Rank < @RankPaging) OR
((Lists#CT.Rank = @RankPaging) AND
((Lists.tp_Modified < @ModifiedPaging) OR
((Lists.tp_Modified = @ModifiedPaging) AND
(Lists.tp_ID > @ItemIdPaging)))))
ORDER BY
CT#Rank DESC,
LastModified DESC,
ItemId ASC
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE proc_FetchListItemSearchResultsBoolean
(
@SearchTerm nvarchar(255),
@SiteId uniqueidentifier,
@WebId uniqueidentifier,
@RankPaging int,
@ModifiedPaging datetime,
@ItemIdPaging uniqueidentifier)
AS
DECLARE @CustSearchTerm nvarchar(255)
SET @CustSearchTerm = '"' + @SearchTerm + '"'
SET NOCOUNT ON
SELECT
UserData.nvarchar1,
UserData.nvarchar3,
UserData.nvarchar7,
Docs.DirName,
Docs.LeafName,
UserData.tp_ID,
UserData.tp_ListId,
Lists.tp_Title,
UserData.tp_Author,
UserData.tp_Editor,
UserInfo.tp_Title,
UserData.tp_Modified AS LastModified,
NULL,
UserData.tp_GUID AS ItemId,
ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)
AS CT#Rank,
0
FROM
UserData
LEFT OUTER JOIN
ContainsTable(UserData, *, @CustSearchTerm) AS UserData#CT
ON
UserData#CT.[Key] = UserData.tp_GUID
LEFT OUTER JOIN
Lists
ON
(Lists.tp_WebId = @WebId) AND
(Lists.tp_ID = UserData.tp_ListId)
LEFT OUTER JOIN
UserInfo
ON
(UserInfo.tp_ID = UserData.tp_Editor) AND
(UserInfo.tp_SiteID = UserData.tp_SiteId)
LEFT OUTER JOIN
Docs
ON
(Docs.SiteId = @SiteId) AND
(Docs.ListId = UserData.tp_ListId) AND
(Docs.DocLibRowId = UserData.tp_ID)
LEFT OUTER JOIN
ContainsTable(UserInfo, *, @CustSearchTerm) AS UserInfo#CT
ON
UserInfo#CT.[Key] = UserInfo.tp_GUID
WHERE
(@WebId = Lists.tp_WebId) AND
((Lists.tp_Flags IS NULL) OR
((Lists.tp_Flags & 0x400) = 0) OR
(UserData.tp_ModerationStatus = 0)) AND
((UserData#CT.Rank IS NOT NULL) OR
(UserInfo#CT.Rank IS NOT NULL)) AND
((@RankPaging IS NULL) OR
(ISNULL(UserData#CT.Rank, 0) +
ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR
((ISNULL(UserData#CT.Rank, 0) +
ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND
((UserData.tp_Modified < @ModifiedPaging) OR
((UserData.tp_Modified = @ModifiedPaging) AND
(UserData.tp_GUID > @ItemIdPaging)))))
UNION ALL
SELECT
Lists.tp_Title,
NULL,
NULL,
Docs.DirName,
Docs.LeafName,
NULL,
Lists.tp_ID,
NULL,
NULL,
NULL,
NULL,
Lists.tp_Modified AS LastModified,
NULL,
Lists.tp_ID AS ItemId,
Lists#CT.Rank AS CT#Rank,
1
FROM
Lists
INNER JOIN
ContainsTable(Lists, *, @CustSearchTerm) AS Lists#CT
ON
Lists#CT.[Key] = Lists.tp_ID
LEFT OUTER JOIN
Docs
ON
Lists.tp_RootFolder = Docs.[Id]
WHERE
(@WebId = Lists.tp_WebId) AND
((@RankPaging IS NULL) OR
(Lists#CT.Rank < @RankPaging) OR
((Lists#CT.Rank = @RankPaging) AND
((Lists.tp_Modified < @ModifiedPaging) OR
((Lists.tp_Modified = @ModifiedPaging) AND
(Lists.tp_ID > @ItemIdPaging)))))
ORDER BY
CT#Rank DESC,
LastModified DESC,
ItemId ASC
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
(Unchecked by Editor)
Thanks, Billy!
The following "improved" version of the above was supplied to me by Paul (of The Netherlands) but the main work had already been done by Carl Lewis when he was taking a break from the long jump (kidding - it was another Carl Lewis).
[Note that it's my fault if the new lines are in the wrong place - it was just too much text to edit carefully for this.]
-------------
The standard WSS search is very annoying as it contains no boolean searches. The way to fix this is to alter the stored procedure that controls the searching. proc_FetchDocSearchResults controls
searching in documents.
The solution I used (found it somewhere, posted by Carl Lewis) consists of 2 stored procedures,
First you replace the proc_FetchDocSearchResults with the code below. (Always make backups!)
Story continues below 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, 5,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,
& |