-1

I have searched if there is a reason why tsql contains wouldnt allow "or not" or "not" as unary operator.

On the microsoft site it just says "OR NOT operator is not allowed" CONTAINS (Transact-SQL)

I can simply put the unary not in outside of contains to be able to do that right?

SELECT * FROM Person 
WHERE NOT CONTAINS(*, 'Steve')

and the same with "or not"

SELECT * FROM Person 
WHERE CONTAINS(FirstName, 'Peter') OR NOT CONTAINS(LastName, 'Smith')

Is it problematic to do queries as in the two examples?

Thanks for your help
Manuel

Manuel Hägeli
  • 13
  • 4

2 Answers2

0

Do you have some specific reason to do Full Text search in your situation?

In your example you might want simplify query to the following:

SELECT * FROM Person
WHERE FirstName LIKE '%Peter%' OR LastName NOT LIKE '%Smith%'

If you need to use Full Text search, you could change query to something like this:

SELECT * FROM Person 
WHERE CONTAINS(FirstName, 'Peter')

EXCEPT

SELECT * FROM Person 
WHERE CONTAINS(LastName, 'Smith')
Kaspars Ozols
  • 6,967
  • 1
  • 20
  • 33
  • But that's much more cumbersome than the one line `WHERE CONTAINS(FirstName, 'Peter') OR NOT CONTAINS(LastName, 'Smith') – Gerd Wagner Jan 28 '14 at 15:04
  • Im using fulltext becouse a user is searching in my case. The user inputs something like "Peter OR NOT Smith" and I transform it into a query – Manuel Hägeli Jan 28 '14 at 15:10
0

Your examples should work.

An expression like CONTAINS( Description, 'NOT "Mountain" ') is probably not allowed because it is equivalent (we can rewrite it) to NOT CONTAINS( Description, 'Mountain').

Similarly, an expression like CONTAINS( Description, '"River" OR NOT "Mountain" ') is equivalent to CONTAINS( Description, 'River') OR NOT CONTAINS( Description, 'Mountain').

Gerd Wagner
  • 5,481
  • 1
  • 22
  • 41
  • As far as I understood _Contains(*, 'Mountain AND River')_ is returning the same result as _Contains(*, 'Mountain') AND Contains(*, 'River')_ but is not equivalent when executed – Manuel Hägeli Jan 28 '14 at 15:16
  • I was referring to logical equivalence, which is, of course, not related to the way how each query is executed. But in such simple cases there shouldn't be any difference in execution time anyway. – Gerd Wagner Jan 28 '14 at 15:19