CHARINDEX in SQL Server 2008 TSQL with inconsistent results

Question

When I run the following code examples: I get different results (Shown below). Please explain why I get a match in the second example? I must be missing some understanding.

print @@version
-- First example behaves as expected
declare @strSearch nvarchar(10) = 'x (20) '; 
declare @definition nvarchar(100) = 'x (200000000) ';
print charindex(@strSearch, @definition);
go
-- Second example does not behave as expected
declare @strSearch nvarchar(10) = 'xrchar (20) '; 
declare @definition nvarchar(100) = 'xrchar (200000000) ';
print charindex(@strSearch, @definition);

Results below: (0 'not found' and 1 'found'):

Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64) Apr 3 2015 14:50:02 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM)

0 1

Zohar Peled Zohar Peled · Accepted Answer · 2017-08-03T05:56:38

The reason you get 1 in the second example is that you are setting a value to your @strSearch that is too long for the variable definition, so sql server trims it to fit the length of the variable (instead of raising a "string or binary data would be truncated" error).
You can see it if you alter the script a little bit:

First example:

declare @strSearch nvarchar(10) = 'x (20) '; 
declare @definition nvarchar(100) = 'x (200000000) ';
SELECT  @strSearch as '@strSearch', 
        @definition as '@definition', 
        charindex(@strSearch, @definition) as 'charindex';

results:

@strSearch  @definition     charindex
x (20)      x (200000000)   0

Second example:

declare @strSearch nvarchar(10) = 'xrchar (20) '; 
declare @definition nvarchar(100) = 'xrchar (200000000) ';
SELECT  @strSearch as '@strSearch', 
        @definition as '@definition', 
        charindex(@strSearch, @definition) as 'charindex';

results:

@strSearch  @definition         charindex
xrchar (20  xrchar (200000000)  1

You can see a live demo on rextester (Turns out that in 2014 that behavior still continues).

CHARINDEX in SQL Server 2008 TSQL with inconsistent results

1 Answers