Is Contains Equivalent To Like In SQL Server

Question

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 Like 'A%' --3 Result

but when I'm running

Select * from Table1 Where Contains(Column1 ,'aaa') --3 Result
Select * from Table1 Where Contains(Column1 ,'a') --0 Result
Select * from Table1 Where Contains(Column1 ,'A') --0 Result

CONTAINS can search for:As Per MSDN

1. A word or phrase.
2. The prefix of a word or phrase.
3. A word near another word.

Does that mean that Contains can't search for letters?

If yes, then how?

Edit2:

declare @param as varchar(20)='a'
select * from table1 where Contains(column1,@param)

This is Working,

declare @param as varchar(20)='"a*"'
select * from table1  where Contains(column1,@param)

But,This is Not

declare @param as varchar(20)='a'
select * from table1  where Contains(column1,@param+'*')

And,

select * from table1  where Contains(column1,'"'+@param+'*"')

H

as per the docs: Searches for precise or fuzzy (less precise) matches to single words and phrases. a would probably count as noise/stop-word and be ignored. try contains(column, 'a*') – Officialdom

You need to include double-quotes in your search string. Look at the examples on the MSDN page that my answer links to. Those should help you out. – Poachy

Answer 1

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

WHERE CONTAINS(Column1 , ' "a*" ');
WHERE CONTAINS(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 @SearchThis varchar(10) = 'A'; 
set @SearchThis = quotename(@SearchThis + '*', '"');
select @SearchThis;

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

WHERE CONTAINS(Column1, @SearchThis)

Chickpea

If Someone Need To Pass The String "A" as a Parameter,Then What Should Be the Syntax? – H

WHERE CONTAINS(Column1, @SearchThis + '*'), Not Working, Incorrect Syntax Near + – H

Answer 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.

Poachy