cliff

I have been searching for code to take search terms and build a CONTAINS
statement. This forum provided a couple of examples that were flawed in one
way or another. I took what I found here (thanks to Hilary) and built
something that works for me (code below). This stored procedure builds a
SQL statement out of entered search terms. It requires a table
(noise_words) that contains the noise words from noise.enu. It handles the
situation of two contiguous spaces, and it allows for a quoted string (using
double-quotes). If the quotes are unbalanced, it assumes a closing quote at
the end of the string. What it does NOT handle are 1) single quotes, 2)
Quoted strings that contain only noise words, and 3) Any concept of OR
logic. I had thought of including logic to interpret a comma as an OR
instead of an AND, but that raised questions as to whether I should also
handle parentheses, and we determined that our application did not require
this level of logic.

So, I'm putting this out here as it may help others, along with the request
that any suggested improvements also be posted.

Thanks.

/* p_create_search_stmt

Create a search statement
Input parameters:
@table_name - the table to search
@col_name - the column to search
@str - the search string
Output parameter:
@rtn-str

Usage is

declare @output nchar(255)
EXEC p_create_search_stmt 'table_name','column_name','"Lincolns Gettysburg
Address" alpha and beta or gamma "War and Peace"', @output output
--print @output
EXEC sp_executesql @output

Above sample query generates the following output:
select * from table_name where contains(column_name,'"Lincolns Gettysburg
Address" AND "alpha" AND "beta" AND "gamma" AND "War and Peace"')

*/

CREATE PROCEDURE p_create_search_stmt
(
@table_name char(255),
@col_name char(255),
@str char(255),
@rtn_str char(255) OUTPUT
)

AS

set nocount on
declare @count int
declare @spid int
declare @modulo int
declare @char char(1)
declare @debug char(255)
declare @debug_num int
declare @string char(255)
declare @wrk_str char(255)
declare @build_str char(255)
declare @test_word char(255)
set @count=0
set @string=''
set @rtn_str=''
set @build_str=''
declare @number_of_characters int
declare @number_of_quotes int

--move input into working variable
set @wrk_str = @str

--calculating the number of quotes
select @number_of_quotes =len(@wrk_str)-len(replace (@wrk_str,'"',''))
--check to see if they are balanced (even number)
select @modulo = @number_of_quotes % 2
--print @modulo
--if they are odd (modulo = 1) then add one at the end
IF (@modulo = 1)
begin
set @wrk_str = rtrim(@wrk_str) + char(34)
set @modulo = 0
end
--print @wrk_str

--calculating the number of characters
select @number_of_characters =len(@wrk_str)

--iterating through the search phrase
WHILE (@number_of_characters > 0)
BEGIN
--check the next character in the string
select @char = substring(@wrk_str,1,1)

--if the character is a quote, place the phrase into build_str
IF (@char = char(34))
BEGIN
--trim the initial quote off
select @wrk_str=substring(@wrk_str,2,255)
select @build_str = rtrim(@build_str) + ' AND ' + char(34) +
rtrim(substring(@wrk_str,1,charindex(char(34),@wrk _str)-1)) + char(34)
--remove phrase from wrk_str
select
@wrk_str=ltrim(substring(@wrk_str,charindex(char(3 4),@wrk_str)+1,255))
--reset number of characters for the loop construct
select @number_of_characters =len(@wrk_str)
--start over with remainder of phrase
CONTINUE
END

ELSE
--else (not a quote), place the next word into build_str
BEGIN
select @test_word =
rtrim(substring(@wrk_str,0,charindex(char(32),@wrk _str)))
--is this word a noise word?
select @count = count(*) from noise_words where word_txt =
@test_word
IF @count = 0
select @build_str = rtrim(@build_str) + ' AND ' + char(34) +
rtrim(substring(@wrk_str,0,charindex(char(32),@wrk _str))) + char(34)
--remove the word from the string
select @wrk_str=ltrim(substring(@wrk_str,
charindex(char(32),@wrk_str),255))
--reset number of characters for the loop construct
select @number_of_characters =len(@wrk_str)
--start over with remainder of phrase
END
END

--add the beginning and end of the query and return
select @build_str = 'select * from ' + rtrim(@table_name) + ' where
contains(' + rtrim(@col_name) + ','+char(39)+substring(@build_str,6,500)
select @rtn_str = rtrim(@build_str) + char(39) + ')'

GO