Re: Need simple search string converted to full-text search string
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Mon, 11 Apr 2005 10:16:18 -0400
What exactly are you looking for?
Here is a proc I use - not sure if it meets with your needs or not.
usage is
declare @NumberOfRoww int exec @NumberOfRows =SearchSQL1 'SearchPhrase' print @numberOfRows
CREATE procedure SearchSQL1 (@stringin varchar(200), @BooleanType int= NULL)
as
-- a @boolean type of null means a phrase based search
-- a @boolean type of 1 means an OR type search
-- a @boolean type of 0 means an AND type search
declare @holdingString varchar(2000)
declare @whitespace int
declare @boolean varchar(10)
set @boolean=case when @booleantype is null then ' ' when @booleantype=1
then char(34)+' OR ' + char(34) else char(34)+' AND '+char(34) end
declare @counter int
declare @posold int
declare @posnew int
set @holdingstring='select * from authors where contains(*,'''+char(34)
select @whitespace=len(@stringin) - len(replace(@stringin,' ',''))
select @posold=0
select @posnew=Charindex(' ',@stringin)
while @whitespace >=0
begin
if @whitespace=0
begin
select @holdingString=@holdingString+substring(@stringin,@posold+1,
len(@stringin)-@posold+1)+char(34)+char(39)+')'
end
else
begin
select @holdingString = case when len(substring(@stringin,@posold+1,
@posnew-@posold-1))>0 then @holdingString+substring(@stringin,@posold+1,
@posnew-@posold-1)+@boolean else @holdingstring end
select @posold=@posnew, @posnew=Charindex(' ',@stringin, @posold+1)
end
select @whitespace=@whitespace-1
end
--print @holdingstring --uncomment for debugging
exec(@holdingstring)
return @@rowcount
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com .
- References:
- Need simple search string converted to full-text search string
- From: David Samson
- Need simple search string converted to full-text search string
- Prev by Date: Need simple search string converted to full-text search string
- Next by Date: reserved (ampersand) characters in full text queries
- Previous by thread: Need simple search string converted to full-text search string
- Next by thread: reserved (ampersand) characters in full text queries
- Index(es):
Relevant Pages
|
