SQL Server 2012 blank string comparison with 0

Question

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

Answer 1

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