3

I have a table called logs.lognotes, and I want to find a faster way to search for customers who do not have a specific word or phrase in the note. I know I can use "not like", but my question is, can you use DOES NOT CONTAINS to replace not like, in the same way you can use:

SELECT *
  FROM table
 WHERE CONTAINS (column, ‘searchword’)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user2785195
  • 31
  • 1
  • 1
  • 3
  • 3
    just use `not contains` – Laurence Sep 16 '13 at 19:46
  • It is just "NOT CONTAINS" I believe I hope this helps. – Bit Sep 16 '13 at 19:51
  • What makes you think it's faster? – PM 77-1 Sep 16 '13 at 19:53
  • 1
    @PM77-1 it is faster. `CONTAINS` can only be used on columns with a `FULL TEXT INDEX` on and can take advantage of the index, whereas `LIKE` with two wildcard operators cannot use indices. This is confirmed on [MSDN](http://technet.microsoft.com/en-us/library/ms187787.aspx) and in at least one other [stackoverflow answer](http://stackoverflow.com/a/7510685/1048425) – GarethD Sep 16 '13 at 20:20

2 Answers2

5

Yes, you should be able to use NOT on any boolean expression, as mentioned in the SQL Server Docs here. And, it would look something like this:

SELECT *
  FROM table
 WHERE NOT CONTAINS (column, ‘searchword’)

To search for records that do not contain the 'searchword' in the column. And, according to

Performance of like '%Query%' vs full text search CONTAINS query

this method should be faster than using LIKE with wildcards.

Community
  • 1
  • 1
stmcallister
  • 1,682
  • 1
  • 12
  • 21
1

You can also simply use this:

select * from tablename where not(columnname like '%value%')
haldo
  • 14,512
  • 5
  • 46
  • 52
Muhammad
  • 21
  • 1
  • I used this instead of the CONTAINS method below because the table was not full text indexed – Ron Dec 11 '22 at 18:30