How to convert LIKE to CONTAINS in SQL Server?

Question

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?

Can you provide a sample table and output? – Tejas Jun 17 '19 at 05:58 — Tejas, Jun 17 '19 at 05:58
Hi @Tejas I added a sample output – Ben.S Jun 17 '19 at 14:20 — Ben.S, Jun 17 '19 at 14:20

score 1 · Accepted Answer · edited Jul 11 '19 at 11:29

1

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.

edited Jul 11 '19 at 11:29

Martijn Pieters

1,048,767
296
4,058
3,343

answered Jun 17 '19 at 08:59

Michael Tobisch

1,034
6
15

Hi @Michael Tobisch, in other words, I can't use CONTAINS to search for a substring inside a larger string? I can only search for a word? – Ben.S Jun 17 '19 at 14:22
It is listed what you can search for. Follow the link above and read the documentation about full-text indexing, please. Maybe [this](https://stackoverflow.com/questions/3400/how-do-you-get-leading-wildcard-full-text-searches-to-work-in-sql-server) is helpful as well. – Michael Tobisch Jun 18 '19 at 08:16

How to convert LIKE to CONTAINS in SQL Server?

1 Answers1