CHARINDEX weirdness

Question

How would you think the following results be?

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;

I was anticipating that result 1 and 2 are the same, and result 3 would be zero, but in fact result 2 and 3 are the same, and they are not zero, and not equal to result 1 either.

What's going on?

I didn't seen an of such precaution in https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?view=sql-server-2017

Answer 1

Catch is here:

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

From char and varchar

"When n is not specified in a data definition or variable declaration statement, the default length is 1."

Should be:

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

---

I was anticipating that result 1 and 2 are the same, and result 3 would be zero

Correct.

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 Demo