create procedure usp_ParseArray
( @Array varchar(1000),
@separator char(1) ) 
AS
-- Created by graz@sqlteam.com
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
create table #array (value varchar(1000) not null)

-- For my loop to work I need an extra separator at the end.  I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0 
begin

  -- patindex matches the a pattern against a string
  select @separator_position =  patindex('%' + @separator + '%' , @array)
  select @array_value = left(@array, @separator_position - 1)

  -- This is where you process the values passed.
  -- Replace this select statement with your processing
  -- @array_value holds the value of this element of the array
  insert into #array
  select @array_value

  -- This replaces what we just processed with and empty string
  select @array = stuff(@array, 1, @separator_position, '')
end

select * from #array
set nocount off
go

create table #search (value varchar(1000))
insert into #search
exec usp_parsearray @search_keyword, ','

delete #search
from #search s
     inner join reservedwordtable rwt
         on s.value = rwt.word

declare @search_keyword_clean varchar(8000)
select
    @search_keyword_clean = coalesce(@search_keyword_clean + ' ', '') + value
from
    #search

CREATE function fn_noise(@str varchar(200),@type varchar(3)='AND')
returns varchar(200)
as
/*
This function is a helper function for FTS search
It splits the search string (using another function fn_Split)
into words and then eliminates noise words by joining to 
the noise table. The noise table ci_title_search_noise is an
import of the FTS noise.eng file and will need to be maintained
to stay in sync with the actual noise file for FTS
This function prevents errors when searches contain noise words
It can be used for AND and OR searches


Initial Creation			J Smith		22 Mar 2002
*/
begin

declare @txt varchar(200)
declare @and char(7) ; set @and = '" '+@type+' "'
declare @t table(word varchar(50))

--Split search text into words - space separator
insert @t(word)
select value from dbo.fn_Split(@str,' ')

--remove noise words by joining to noise table
delete from @t 
from @t t join ci_title_search_noise s (nolock)
on t.word=s.word

--reassemble string
SELECT @txt =  COALESCE(@txt +'*'+ @and,'') + word  FROM @t
SELECT @txt = '"'+@txt+'*"'

return @txt
end
go