1

I am running this query on SQL Server 2012:

select 'weird' 
where '' = 0

It's returning 'weird'.

As far as I understand, '' is quite different from 0. So please explain why the above happens.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • possible duplicate of [SQL Server 2008 Empty String vs. Space](http://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space) – Fredou Aug 05 '14 at 15:19

1 Answers1

3

So, taking a look at the data types where they stand in the WHERE clause

SELECT SQL_VARIANT_PROPERTY(0, 'BaseType'),SQL_VARIANT_PROPERTY('', 'BaseType')

They return int, varchar respectively.

When comparing two different data types, the data type with the lower precedence will convert to the higher precedence, per MSDN.

In this case, Varchar converts to int.

select cast('' AS int)

The above returns 0.

Thus 

select 'weird' where 0 = 0
Elias
  • 2,602
  • 5
  • 28
  • 57