February 8, 2006 at 6:38 pm
Why and when one would choose to use CONTAINS instead of
LIKE %searchstring%? Thanks
February 8, 2006 at 9:10 pm
Depends on your application. Contains is a more powerful utility.
February 10, 2006 at 7:16 am
I believe that CONTAINS can only be used when you have a full text index on the field. LIKE is used more often for char fields, or fields without an index.
February 10, 2006 at 8:15 am
Yes, you have to create a full-text catalog and populate it before using Contains(). You'd have to do something along these lines:
USE <your database>
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog '<your_FT_CatName>' , 'create'
EXEC sp_fulltext_table '<your Table>', 'create', '<your_FT_CatName>, 'primaryKeyCol'
EXEC sp_fulltext_column '<your Table>' , '<text column>' , 'add'
EXEC sp_fulltext_catalog '<your_FT_CatName>', 'start_full'
Once, indexed, Contains() run very quickly. But beware that since it is a word-based search, you can't use it to find embedded or trailing substrings. For example, you can search for rows where the text column contains a word that begins with 'serv' ( CONTAINS ( *, ' "serv*" ), but not words that end in 'rver': for example, CONTAINS ( *, ' "*rver" ) won't return the desired rows.
For those types of searches, you'll still have to use the much slower LIKE.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply