sql - 包含相当于 SQL Server 中的 Like

Question

当我运行此查询时:

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

但是当我运行时

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 可以搜索:As Per MSDN

1. 一个单词或短语。
2. 单词或短语的前缀。
3. 一个词靠近另一个词。

这是否意味着 Contains 无法搜索字母？

如果是，那么如何？

编辑2:

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

这是工作，

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

但是，这不是

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

并且，

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

Answer 1

您需要使用星号来执行前缀搜索:

WHERE CONTAINS(Column1 , ' "a*" ');
WHERE CONTAINS(Column1 , ' "A*" ');

除此之外，CONTAINS 还受到停用词过滤器的影响。阅读这些 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.

要将输入作为参数传递，只需附加星号:

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

完成 SearchThis 设置后，您可以在以下位置使用:

WHERE CONTAINS(Column1, @SearchThis)