How do i write this query "CONTAINS (text, 'FORMSOF(INFLECTIONAL, something)',LANGUAGE 1033)" if i want to replace the word "something" with a varaibel?

just replace the word with variabel like "CONTAINS (text, 'FORMSOF(INFLECTIONAL, @freetext)',LANGUAGE 1033)" does not work.


Thanks in advance

Is the first example posted in the BOL entry for CONTAINS what you want?

-PatP

yes kind of... this works fine"CONTAINS(Description, @SearchWord)" but i also want the "'FORMSOF(INFLECTIONAL, @freetext)',LANGUAGE 1033)" part. But i can't find the correct syntax.

my bad

i want example "H. Using CONTAINS with variables" with "(text, 'FORMSOF(INFLECTIONAL, @freetext)',LANGUAGE 1033)" part

i ask in another way.

How can i use CONTAINS with variabel and search with multiple words?

I use CONTAINS in a WHERE statement and it works just fine with one search word or if i use AND OR NOT NEAR, no problem. But if i write "word1 word2" i got error:
Msg 7630, Level 15, State 3, Procedure...

how can i avoid this error message when people search like i described?
i have tried to put in this to get rid of some stuff the users put in to separate the search criteria the use:
set @search = replace(@search, '?','*')
set @search = replace(@search, '!','*')
set @search = replace(@search, ',','*')
set @search = replace(@search, ';','*')
set @search = replace(@search, '/','*')
set @search = replace(@search, ':','*')

To get rid of the error message when they put in blanks (or if the row contains NULL) i use:
IF ISNULL(@p_search,'') = '' SET @search= '""';

How can i achieve this?
1. Get rid of the error message when they put in "wrong" search statement?
2. Make the users to be able to search for multiple words in one string?


thx in advance

FREETEXT (Transact-SQL)

-PatP

yes FREETEXT works fine with multiple words but not so well with Boolean search expression's.
Is there any way to have both Boolean search with multiple words?

Do you mean like example J in CONTAINS (Transact-SQL) ???

-PatP

im trying to get a google-like search.
Where i can put in Boolean search expression like OR, AND, NEAR and also single words like [dbforums phelan AND pat]

The problem is that [dbforums phelan AND pat] is in fact a perfectly legitimate search expression. It parses into two string operands and an AND operator.

Is that what you want, or did you expect three strings and two operators. If you expected another operator, which one (AND, OR, NOT, NEAR) did you expect?

The problem comes from trying to match English (or any spoken language) to a parsable logical expression. There isn't always a clear one-to-one match between them and unfortunately computers usually require that simple-minded one-to-one match in order to work well.

-PatP

thanks for the effort.

In my mind i have expect the string like [dbforums AND phelan AND pat] so that menas that blanks should be AND and everything else whichever operator i put in (AND, OR, NOT NEAR) and even search for exact phrases like "dbforums pat phelan" using quotation marks.

Can anyone give me a hint or push me in right direction reagarding this?