Google style search

Is there a way to do a google style search in SQL.
For example if I have a search field and someone puts in:
safe car
It will automatically search like this
contains(*,'"safe*" AND "car*"')
Or
if they put in
"safe car"
it would search in SQL like contains(*,'"safe car"')
Or
if they put in
safe -car AND dog
it would search in sql like contains(*,'"safe*" AND "dog*" AND NOT
"car"')
etc.
I guess I am looking for a regular expression or a script that would
parse the input field and output the user's query in a SQL server
acceptable format.
Google does a strict phrase based query, so safe car is searched as "safe"
or "car"
However to do what you want you would do the following:
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 a phrase based search
-- a @.boolean type of 1 means an OR type search
-- a @.boolean type of 2 means an AND type search
-- a @.boolean type of 3 means an OR wildcarded 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)
WHEN @.booleantype=3 THEN char(34)+' OR ' + 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
if @.booleanType =3
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=@.holdingString+SUBSTRING(@.stringin, @.posold+1,LEN(@.stringin)-@.
posold+1)+char(34)+char(39)+',200) AS t ON '
end
print @.holdingString
END
ELSE
BEGIN
if @.booleantype=3
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
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
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=SearchSQL2 'this is a test',3
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
"Chuck P" <pe@.temp.gov> wrote in message
news:MPG.1cbcaa2b92fc73ee98969b@.news.microsoft.com ...
> Is there a way to do a google style search in SQL.
> For example if I have a search field and someone puts in:
> safe car
> It will automatically search like this
> contains(*,'"safe*" AND "car*"')
> Or
> if they put in
> "safe car"
> it would search in SQL like contains(*,'"safe car"')
> Or
> if they put in
> safe -car AND dog
> it would search in sql like contains(*,'"safe*" AND "dog*" AND NOT
> "car"')
> etc.
> I guess I am looking for a regular expression or a script that would
> parse the input field and output the user's query in a SQL server
> acceptable format.
|||thanks, Hillary
but I wanted to have more of the Google features
like searching for quoted phrases and using not or -
I think it will be a long process