0

Can anyone explain to me why the following returns 'Not equal'?

DECLARE @X15 varchar(15) = 'ABC'
DECLARE @X varchar = @X15

SELECT CASE WHEN @X = @X15 THEN 'Equal' ELSE 'Not equal' END

I must be missing something obvious.

GSerg
  • 73,524
  • 17
  • 153
  • 317
Eric
  • 1,984
  • 2
  • 14
  • 16

2 Answers2

4

If you print out @X you'll see the problem:

DECLARE @X15 varchar(15) = 'ABC'
DECLARE @X varchar = @X15

SELECT LEN(@X), @X, CASE WHEN @X = @X15 THEN 'Equal' ELSE 'Not equal' END

If you don't specify a length for varchar it defaults to 1 character so your @X variable can only hold the first character of @X15.

David
  • 33,527
  • 3
  • 66
  • 80
  • Ahhh, so varchar isn't truly a dynamic variable character pointer, it's really just varchar(1) in disguise. It works if I go the other extreme: varchar(max). Thanks. – Eric Feb 22 '18 at 17:26
  • 1
    @Eric You might or might not be interested in https://stackoverflow.com/q/7141402/11683 and https://stackoverflow.com/q/2009694/11683. – GSerg Feb 22 '18 at 17:33
0

The default value of n is 1 for the char and varchar data types when they are used in variable declaration.

Ex:-

DECLARE @myVariable AS varchar = 'abc';  
DECLARE @myNextVariable AS char = 'abc'; 
DECLARE @myVariableWithLength AS varchar(15) = 'abc'; 
--The following returns 1  
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable),DATALENGTH(@myVariableWithLength);  
GO
Jayesh Goyani
  • 10,753
  • 11
  • 27
  • 49