CHARINDEX怪异

Question

你认为以下结果如何？

SELECT CHARINDEX('space and /* comment */', 'Phrase with space and /* comment */') AS MatchPosition;
DECLARE @SearchWord varchar =  'space and /* comment */'
SELECT CHARINDEX(@SearchWord, 'Phrase with space and /* comment */') AS MatchPosition;
SELECT CHARINDEX(@SearchWord, 'Phrase with space and comment') AS MatchPosition;

我预期结果1和2是相同的，结果3是零，但实际上结果2和3是相同的，它们不是零，也不等于结果1。

到底怎么回事？

我在https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?view=sql-server-2017没有见过这样的预防措施

Answer 1

渔获物在这里：

DECLARE @SearchWord varchar =  'space and /* comment */'
SELECT @SearchWord;
-- s

来自char和varchar 当数据定义或变量声明语句中未指定n时，默认长度为1.

应：

-- defining size to avoid implicit truncating
DECLARE @SearchWord varchar(100) =  'space and /* comment */'
SELECT @SearchWord;

我预期结果1和2是一样的，结果3是0。

对，是这样。

SELECT CHARINDEX('space and /* comment */', 'Phrase with space and /* comment */') AS MatchPosition;
DECLARE @SearchWord varchar(100) =  'space and /* comment */'
SELECT CHARINDEX(@SearchWord, 'Phrase with space and /* comment */') AS MatchPosition;
SELECT CHARINDEX(@SearchWord, 'Phrase with space and comment') AS MatchPosition;

Rextester演示