Is Contains Equivalent To Like In Sql Server

When I'm running this query: Select * from Table1 Where Column1 Like 'aaa%' --3 Result Select * from Table1 Where Column1 Like 'a%' --3 Result Select * from Table1 Where Column1 Li

Solution 1:

You need to use an asterisk to perform a prefix search:

WHERECONTAINS(Column1 , ' "a*" ');
WHERECONTAINS(Column1 , ' "A*" ');

In addition to this, CONTAINS is subject to stopword filters. Read up on those here

A stopword can be a word with meaning in a specific language, or it can be a token that does not have linguistic meaning. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-text index since they are known to be useless to a search.

To pass input as a parameter, just append the asterisk:

declare@SearchThisvarchar(10) ='A'; 
set@SearchThis= quotename(@SearchThis+'*', '"');
select@SearchThis;

Once you have the SearchThis setup, you can use in where:

WHERECONTAINS(Column1, @SearchThis)

Solution 2:

CONTAINS is much more powerful than LIKE. From MSDN...

Comparison of LIKE to Full-Text Search

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned and their size. Another consideration is that LIKE performs only a simple pattern scan of an entire table. A full-text query, in contrast, is language aware, applying specific transformations at index and query time, such as filtering stopwords and making thesaurus and inflectional expansions. These transformations help full-text queries improve their recall and the final ranking of their results.

For your specific concern, you need the prefix search that other answers indicate. But head to the MSDN page that I linked. It will help you out.