Why does SQL try to convert a string to int rather than int to string for comparisons?

Question

These queries return unexpected results for me.

SELECT 'Test' WHERE '00001000' = 1000
SELECT 'Test' WHERE '00001000' = '1000'
SELECT 'Test' WHERE 0001000 = 1000

I would expect it to be no results, no results, results, but instead the first query returns results. It seems that SQL will try and convert the string to an int to do the comparison, instead of the other way around. Is there a reason for this?

marc_s · Accepted Answer · 2015-07-31 19:37:02Z

3

Based on the Data Type Precedence in SQL Server, whenever it encounters two differing datatypes on either side of an operator, it will try to convert both values to the data type with the higher preference.

In the case of int vs. varchar, it is int - so SQL Server will try to convert both side of the equal sign to an int and then compare - and in the case of your first statement, those values are in fact the same, so you get a result.

answered Jul 31, 2015 at 19:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does SQL try to convert a string to int rather than int to string for comparisons?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related