How to convert LIKE to CONTAINS in SQL Server?

This is a query using 'LIKE':

  SELECT JoinedColumnInfo FROM [dbo].[ImportedData]
  WHERE CategoryId = 11131700
  AND  JoinedColumnInfo LIKE  '%usb%'

The 'LIKE' result table is:

1.F5TYEU 2815124 KOSS cs100usb Double Sided Comm Headset USB, Noise Cancelling Microphone Wired

2.PODCASTUDIOUSB BEHRINGER Studio PodCast Kit - Includes Microphone, Mixer and Headphones

3.NUYVSRB 79X3419cs100usb KOSS Double Sided Comm Headset USB, Noise Cancelling Microphone

And this is the same query using 'CONTAINS':

   SELECT JoinedColumnInfo FROM [dbo].[ImportedData]
   WHERE CategoryId = 11131700
   AND  CONTAINS  (JoinedColumnInfo,   '"usb*"')

The CONTAINS result table is: (2 results)

1.F5TYEU 2815124 KOSS cs100usb Double Sided Comm Headset USB, Noise Cancelling Microphone Wired

3.NUYVSRB 79X3419cs100usb KOSS Double Sided Comm Headset USB, Noise Cancelling Microphone

In the CONTAINS query I don't get the record number 2 why? how can I convert from 'LIKE' to 'CONTIANS' and get the same results?

Solution

There are some fundamental differences in how LIKE and CONTAINS work, the most important is that you need an existing full-text index on the column when using CONTAINS.

From here:

CONTAINS can search for:

A word or phrase.

The prefix of a word or phrase.

A word near another word.

A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

LIKE on the other hand uses pattern matching to find simple string matching to find the value you are looking for.