Geek asks and answers

T-SQL, string comparison with leading digits using parameters returns false positive

I was wondering if anyone could explain this behavior?

DECLARE @RandomParam1 NVARCHAR
DECLARE @RandomParam2 NVARCHAR
DECLARE @RandomParam3 NVARCHAR
SET @RandomParam1 = '0HelloWorld'
SET @RandomParam2 = '9HelloWorld'
SET @RandomParam3 = '15HelloWorld'

select 1 where '0' = @RandomParam1  -- true
select 1 where '0' = '0HelloWorld'  -- false

select 1 where '9' = @RandomParam2  -- true
select 1 where '15' = @RandomParam3 -- false

Why does comparing strings with parameters give a different result than without parameters? And why does he claim that '0' = '0whatever'?

I get that it may be that the parameter is trying to compare it as numbers, but then the last example should also be true.

Any ideas?

+3
source share
2 answers

the default length for NVARCHARis 1. All three parameters effectively contain just one character.

If you change your ads to

DECLARE @RandomParam1 NVARCHAR(32)
DECLARE @RandomParam2 NVARCHAR(32)
DECLARE @RandomParam3 NVARCHAR(32)

you will get the behavior you expect.

nchar and nvarchar

n , 1.

+9

, 1.

DECLARE @RandomParam1 NVARCHAR(20)
DECLARE @RandomParam2 NVARCHAR(20)
DECLARE @RandomParam3 NVARCHAR(20)

LENGTH .

+2

More articles:


All Articles