CODEWITHSUNDEEP
sqlsql-server
anar khalilov
anar khalilov

Reputation: 17498

VARCHAR vs VARCHAR(X)

This results in 'B':

DECLARE @NAME VARCHAR(20)=' s'
IF (@NAME IS NULL OR @NAME='')
   SELECT 'A'
ELSE
   SELECT 'B'

Whereas, this results in 'A'.

DECLARE @NAME VARCHAR=' s'
IF (@NAME IS NULL OR @NAME='')
   SELECT 'A'
ELSE
   SELECT 'B'

The only difference is VARCHAR(20) vs VARCHAR.

What is the reason of this odd behaviour?

Upvotes: 1

Views: 295

Answers (2)

StuartLC
StuartLC

Reputation: 107267

Sql Server defaults a VARCHAR of unspecified length to a length of 1. And when taken in conjunction with Microsoft's interpretation of ANSI/ISO SQL-92 (ref here) which results in padding compared strings to equal length during equality comparisons, resulting in ' ' being = to '', hence the non-intuitive 'A' in the second test.

Upvotes: 4

Consult Yarla
Consult Yarla

Reputation: 1150

VARCHAR needs a size to be specified when there is no size specified it assume that the declaration is of one character length.

To confirm this, you can check the length of the variables.

Since, your if condition is checking for '' it satisfies the condition.

DECLARE @NAME VARCHAR=' s'
select datalength(@name)

returns 1 

DECLARE @NAME VARCHAR(20)=' s'
select datalength(@name)

returns 2

Upvotes: 0

Related Questions