Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: help with CONTAINS syntax

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is the first example posted in the BOL entry for CONTAINS what you want?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    my bad

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

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    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?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you mean like example J in CONTAINS (Transact-SQL) ???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Mar 2004
    Posts
    162
    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]

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Mar 2004
    Posts
    162
    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.

  12. #12
    Join Date
    Mar 2004
    Posts
    162
    Can anyone give me a hint or push me in right direction reagarding this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •