Why do I get the error:

System.Data.SqlClient.SqlException: Syntax error occurred near 'clerk'.
Expected ''''' in search condition 'Benefits clerk'.

I get this from:

.... contains(jobdescription,'Benefits clear')

Thanks,

Tom

Tom,
You have multiple word or phrase search condition and you need to wrap both
words within double quotes, for example:

.... contains(jobdescription,'"Benefits clear"')

This will return the phrase "Benefits clear" in the FT-enable column
jobdescription, if this phrase exists in this column. You might find the
following KB article helpful too: 246800 (Q246800) "INF: Correctly Parsing
Quotation Marks in FTS Queries" at
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/



[quoted text, click to view]

[quoted text, click to view]

That makes sense.

But what I want to be able to do is allow the user to put in -Benefits Clear
Payroll- in the text field and look for articles with Benefits and Clear and
Payroll in them, as well as deal with someone putting in Benefits and Clear
and Payroll.

I assume I would have to replace the blanks with "and" to make it work.

I played with the article you suggested and it shows how to surround "and
not" with double quotes as well as "or not". I am confused as to why you
would surround "and" and "or" with quotes - 'benefits and Clear' work.

Is there a regular expression that says to replace the blank with "and" only
if the 2 words are not (and,or,near) ?

Thanks,

Tom
[quoted text, click to view]

have a look at this link. It is some javascript in an html page which
will remove the noise words.

Then you would have to execute a proc like this one. If you pass it a
parameter of 1 it will do what you are looking for

searchsql1 'test test1 test2',2

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 2 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
exec(@holdingstring)
return @@rowcount

this one incorporates containstable which allows you to limit your
results set and you can order by rank.

CREATE PROCEDURE SearchSQL2 (@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

[quoted text, click to view]

I found a way to make this sort of work (but not very elegant).

testLabel.text = "sales near marketing and database"

testLabel.text = Regex.Replace(testLabel.text, "( )", " and ")
testLabel.text = Regex.Replace(testLabel.text," and and "," ")
testLabel.text = Regex.Replace(testLabel.text,"and or and","or")
testLabel.text = Regex.Replace(testLabel.text,"and near and","near")

This will change all blanks to " and ". It will then look for and, or and
near and put them back as they would be surrounded by " and ".

My problem is phrases.

If I have something like:
sales "marketing franchise" dealer

I would get:

sales and "marketing and franchise" and dealer

Is there a way of telling the regex to ignore changes inside quotes?

Thanks,

Tom
[quoted text, click to view]

[quoted text, click to view]

What link?

[quoted text, click to view]