Re: Front end website full-text search
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Thu, 31 Mar 2005 21:01:58 -0500
But if it does an AND it will not be the same as "sql server" which searches
for the two words occurring adjacent to each other.
Here is a proc which you can set whether you want the entire phase, an OR,
or an AND.
CREATE PROCEDURE SearchSQL (@stringin varchar(200), @BooleanType int= NULL)
AS
-- a @boolean type of null means a phrase based search
-- a @boolean type of 0 means an OR type search
-- a @boolean type of 1 means an AND type search
DECLARE @holdingString VARCHAR(2000)
DECLARE @whitespace INT
DECLARE @boolean VARCHAR(10)
--returning a syntax message if no search phrase is passed
IF LEN(@stringin)=0
BEGIN
PRINT 'usage is SimpleSQLFTSSearch ''Your Search Phrase goes here'''
RETURN -1
END
SET @boolean=case WHEN @booleantype=1 THEN char(34)+' OR ' + char(34)
WHEN @booleantype=2 THEN char(34)+' AND ' + char(34)
ELSE ' ' END
DECLARE @counter INT
DECLARE @posold int
DECLARE @posnew int
SET @holdingstring='SELECT * FROM authors AS a JOIN
CONTAINSTABLE(authors,*,'''+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)+',200) AS t ON '
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
SELECT @holdingString = @holdingString + 't.[KEY]=a.au_id ORDER BY RANK
DESC'
--PRINT @holdingstring
EXEC(@holdingstring)
RETURN @@rowcount
Usage is:
DECLARE @returncode int
EXEC @returncode=SearchSQL 'this is a test'
PRINT @returncode
--
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
"JT" <jtreuting@xxxxxxxxx> wrote in message
news:1112316885.228212.171380@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> I have a front-end search set up that takes the users input and runs a
> stored procedure that uses FREETEXTTABLE to get the results. I want it
> so that when a user types in a search that has words grouped together
> inside quotes (i.e. "sql server") it does an AND instead of an OR. I
> know that CONTAINSTABLE can have this kind of logic but I was wondering
> if there is a way to handle this without having my script (ASP)
> building the search phrase by parsing the user input.
>
> Any ideas? Or has someone done this and have some advice.
>
> Thanks.
>
.
- References:
- Front end website full-text search
- From: JT
- Front end website full-text search
- Prev by Date: Front end website full-text search
- Next by Date: Full-Text Results to MS Access
- Previous by thread: Front end website full-text search
- Next by thread: Full-Text Results to MS Access
- Index(es):
Relevant Pages
|
