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
  • 76,472
  • 17
  • 159
  • 346
Eric
  • 2,207
  • 2
  • 16
  • 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
  • 34,223
  • 3
  • 62
  • 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
  • 11,008
  • 11
  • 30
  • 50