CODEWITHSUNDEEP
sqlt-sql
xpt
xpt

Reputation: 22994

CHARINDEX weirdness

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

Upvotes: 4

Views: 99

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

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

Upvotes: 8

Related Questions