Author: DB Dave

In this post I’m going to deal with another piece of the puzzle that I described in part 1. Now, after re-reading that post it became obvious that I probably need to outline what the end result is meant to look like:

We want to be able to pass in a search string, and get back a nicely formatted full-text search clause which makes full use of Boolean operators, respects parentheses, and supports wildcards. Easy, right? Yup, and we need to do it all using only T/SQL.

Here’s the steps that make up the final function (in a nutshell):

1. Remove some dodgy characters (vertical-tab, form-feed, etc)
2. Tidy up any parentheses [part 1]
3. Replace any alternate operators with place-holders (most operators have 2 representations; e.g. OR and |, AND and &, NOT and –, etc).
4. Break the search term into individual words (i.e. split the string on space characters)
5. Using this list of individual words, do the following:
   1. Classify each word as an operator, search term, parenthesis, wildcard, etc.
   2. Tidy up the list, removing illegal and unnecessary characters
   3. Remove invalid operators (e.g. “ipod NOT” is not a valid search term)
   4. Remove unnecessary parentheses (e.g. single words wrapped in parentheses)
   5. Handle wildcards. Since full-text only accepts prefix searches, change all invalid wildcard searches into prefix searches.
6. Check for single and double-quotes (& tidy up any invalid quote-marks, make sure only valid pairs remain, etc.).
7. Process any NEAR(~) operators.
8. Put the final Full-Text command string together.

Right, so we’ve already taken care of number 2 in the previous post, so now on to number 3 – replacing the alternate operators.  This step may not be necessary for you, since it’s sole purpose is to preserve the original search string for our reporting purposes.

You see, if I search for “ipad OR iphone NOT ipod” and you search for “ipad | iphone –ipod”, then obviously since they’re really the same search we will get the same results (and our cache should also cache them as 1 search), but for reporting purposes we want to know that our searches were typed in differently.  The way we do that in our implementation of this function, is that we return a normalised search string used to perform & cache the search, and an original string used for reporting.

Here’s the very straight forward code:

IF OBJECT_ID('[dbo].[fn_search_clause_operator_replacer]') IS NULL
EXEC('CREATE FUNCTION [dbo].[fn_search_clause_operator_replacer] () RETURNS INT AS BEGIN RETURN(0); END ');
GO
/***************************************************************************************************************************
This is a supporting function, which is called by the function [fn_search_clause_get].
It accepts a string input, and outputs the same string, but with any character operators replaced with
placeholder text-operators instead.
Author : David Curlewis
Date : 07/2009

This work is licensed under a Creative Commons Attribution-NonCommercial 3.0 Unported License
http://creativecommons.org/licenses/by-nc/3.0/
****************************************************************************************************************************/
ALTER FUNCTION [dbo].[fn_search_clause_operator_replacer] (
@search_text NVARCHAR(4000)
) RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @not_pattern VARCHAR(50),
@not_pos INT;

SET @search_text = REPLACE(REPLACE(REPLACE(@search_text,
'|', ' `OR` '), -- OR
'~', ' `NEAR` '), -- NEAR
'&', ' `AND` '); -- AND

-- the NOT operator requires some extra work because we only consider it an operator if it is not hyphenating a word
SET @not_pattern = '%[^a-z0-9][-][ a-z0-9"''(]%'; -- valid NOT operator pattern
SET @not_pos = ISNULL((SELECT PATINDEX(@not_pattern, @search_text)), 0); -- get the positions of any valid "-" operators
WHILE (@not_pos > 0)
BEGIN
SET @search_text = STUFF(@search_text, @not_pos, 2, ' `NOT` ');
SET @not_pos = ISNULL((SELECT PATINDEX(@not_pattern, @search_text)), 0); -- get pos of next operators (if any)
END

RETURN(@search_text);
END
GO

The NOT operators require a little more work because we don’t want to interpret all hyphens as operators, otherwise you wouldn’t be able to search for hyphenated words. Using PATINDEX we can very easily match on a simple RegEx-like pattern: [^a-z0-9][-][ a-z0-9″”(]

This looks for any occurrences of the hyphen, where it follows a non-alphanumeric character, and precedes a space, an alphanumeric character, a double or single quote, or an opening parenthesis. It then replaces each occurrence with the placeholder text `NOT`.

In the next post I’ll go through the meatiest bit, which involves splitting the string (number 4), and preparing the resulting data (number 5).

Cheers

DB Dave

Part 1: Cleaning up parentheses in a string
Part 2: Replacing alternate operators
Part 3: Splitting the search string
Part 4: Wrapping it up

A few years ago I wanted to have a crack at improving one of our websites’ search by providing “search operator” functionality.  These are basically special words or characters (i.e. operators) which provide Boolean search capability; e.g. AND, OR, NOT.

I looked around but couldn’t find anything implemented using T/SQL.  There were a few .Net solutions, but I’m not a developer, and can only handle very basic .Net on a good day.  So I had a crack at writing my own. I started off with the Google cheat-sheet as my initial list of requirements, which was basically as follows:

1. Must support the operators AND, OR, NOT, and NEAR
2. Must allow for “exact-phrase” searches
3. Should respect parentheses (for precedence control)
4. Should be able to perform wildcard searches
5. Operators must be case-sensitive (i.e. OR != or)

To attack this problem, I decided to split it into multiple chunks, and write separate blocks of code to meet each requirements.  In this post I’m going to introduce the first piece of the puzzle (which is number 3 from the list above; handling parentheses).  What this means is that given a string containing parentheses, I need to output the same string with valid parentheses intact (and obviously invalid ones removed).

Below is the function. I won’t explain it line by line since its pretty well commented, and relatively simple anyway.

IF OBJECT_ID('[dbo].[fn_search_clause_parentheses_handler]') IS NULL
EXEC('CREATE FUNCTION [dbo].[fn_search_clause_parentheses_handler] () RETURNS INT AS BEGIN RETURN(0); END ');
GO
/***************************************************************************************************************************
This is a supporting function, which is called by the function [fn_search_clause_get].
It accepts a string input, and outputs the same string, but with any invalid parentheses removed, and all remaining
valid parentheses buffered (by a space on each side) so that they are split correctly by the calling function.
Author : David Curlewis
Date : 07/2009

This work is licensed under a Creative Commons Attribution-NonCommercial 3.0 Unported License
http://creativecommons.org/licenses/by-nc/3.0/
****************************************************************************************************************************/
ALTER FUNCTION [dbo].[fn_search_clause_parentheses_handler] (
@search_text NVARCHAR(4000)
) RETURNS NVARCHAR(4000)
AS
BEGIN
-------------------------------------------------------------------------------------------------
-- PARENTHESES HANDLER
-------------------------------------------------------------------------------------------------
-- IF parentheses exist in the string
IF (PATINDEX('%[()]%', @search_text) > 0)
BEGIN
DECLARE @pos_o INT, @pos_x INT, @charpos INT;
DECLARE @tt_char_split TABLE (id SMALLINT IDENTITY(1,1), [char] VARCHAR(2) NOT NULL);
SET @charpos = 1;

-- split the string apart into a temp table
WHILE @charpos <= LEN(@search_text)
BEGIN
INSERT @tt_char_split ([char])
SELECT SUBSTRING(@search_text, @charpos, 1);

SET @charpos = @charpos + 1;
END

-- while we have opening and closing parentheses
WHILE EXISTS(SELECT TOP 1 1 FROM @tt_char_split WHERE [char] = '(')
AND EXISTS(SELECT TOP 1 1 FROM @tt_char_split WHERE [char] = ')')
BEGIN
-- Get the position of the first closing parenthesis
SET @pos_x = ( SELECT MIN(id)
FROM @tt_char_split
WHERE [char] = ')');

-- Get the position of the first opening parenthesis
SET @pos_o = ( SELECT MAX(id)
FROM @tt_char_split
WHERE [char] = '('
AND id < @pos_x);

-- there is a valid pair of parentheses
IF (@pos_o IS NOT NULL AND @pos_x IS NOT NULL)
BEGIN
-- Escape this pair so we know they've been processed
UPDATE @tt_char_split
SET [char] = '(('
WHERE id = @pos_o;

UPDATE @tt_char_split
SET [char] = '))'
WHERE id = @pos_x;
END
ELSE BEGIN -- there is not a valid pair of parentheses
UPDATE @tt_char_split
SET [char] = ''
WHERE id IN (@pos_o, @pos_x);
END
END

-- remove any remaining (invalid) parentheses
UPDATE @tt_char_split
SET [char] = ''
WHERE [char] IN ('(', ')');

SET @search_text = '';

-- build new search string
SELECT @search_text = @search_text + REPLACE(REPLACE([char], '((', ' ( '), '))', ' ) ')
FROM @tt_char_split
ORDER BY id;

-- remove empty pairs, i.e. " ( ) "
WHILE CHARINDEX(' ( ) ', @search_text) > 0
SET @search_text = REPLACE(@search_text, ' ( ) ', '');
END

RETURN(@search_text);
END
GO

So, its a scalar function which accepts a string, and returns that same string, but with it’s parentheses tidied up and “buffered” (i.e. I wrap them in spaces to make it easier for the calling function to then split the string into its component parts).

Now, as I sit here watching the latest straight-to-dvd masterpiece that is “Barbie: A fashion fairy-tale” (hey, its a Sunday afternoon and I have 3 daughters – you’d lose the battle too!), I’m having a hard time getting the code to post properly, so if you have any issues, flick me a note in the comments below and I’ll sort something else out.

Give it a try and let me know if you find any bugs (or make any improvements!). I’ll follow up soon with posts describing the rest of the process, but for now I’ve had enough of Barbie, and think I deserve a whisky.

Cheers

DB Dave