Integer has implicit conversion?

You can specify the default by using the "DEFAULT" keyword when you created the column.  It is 0 by default, unless you change it.

. My coworker says it's because for an integer ‘’, blank, or null are 0.

that statement is partially true in mysql, but not in ms sql server, at least not that I  aware.
if the field is varchar, containing '000', for example, it will match, as that string value can silently by casted to the integer value 0

The field is integer, null. I populated the table from the prod database.

Yeah, I can set it to default but wondering why ='' and =0 both bring back the same values.

Think of Excel.  If you try to add a blank cell and a cell with a number in it, you will get the number instead of an error.  This is by design to allow you to add them together.  If it did not automatically case the '' fields, then you would get an error when you try to sum the column.

accordibg to the docs, inconsistent

SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

https://msdn.microsoft.com/en-us/library/ms187928.aspx

away from a pc, i cannot test on a sqlfiddle or so...

This still brought back values

 select * from loads
where cast (SettledWeight as int) = ''

I brought the data back from SQl 2005 and that field is numeric(10,0) in the old database. Not sure if that would make a difference.

The zeros I see are correct but just want to understand why =0 and ='' bring back data. I would think ='' shoudn't bring back anything.

Thanks, Chris! this is great. let me take a look and run some tests.

i see my collegues found the answer
however, it must be clearly said that trying to use intcol = '' is NEVER to be used, like any other implicit conversion, in general

Good comment from Guy, implicit conversions should be avoided everywhere as a matter of good coding practice.  You should always know your data and explicitly convert when necessary so you know what the results should be.
*edit
And also from Scott, NULL is not the same as 0 and SQL does not treat it that way.

Thanks, guys. Let me read.

I disagree with "always" avoiding implicit conversion.  Indeed, for date/time/datetimes, I believe you should always use character values, thus forcing implicit conversion, when you know the literal value will be valid.

For example, I would go so far as to do this to compare for current day:
WHERE date_column >= CONVERT(char(8), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), 112) AND
      date_column < CONVERT(char(8), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0), 112)

Yes, SQL will be forced to implicitly convert the strings to dates, but that's far better than coding explicit conversions or potentially forcing the column to be implicitly converted to a "higher" data type, which is the biggest thing to avoid, because that prevents best use of potential indexes.

and I allow to disagree there, a bit
in most application coding, you would define those parameters as typed, and not as ad-hoc sql string. both only to avoid sql injection, but also for query execution plan sharing (saving server resources and saving compilation time...)
but discussing all this would lead far off the iriginal question.

Late to this 'party' let me just add my opinion about implicit conversions: If they are very straight forward and there is no straight forward way to write data in the natural type it's okay to use them, you don't always do them from a client side frontend language with paramterization, ad hoc queries are often good enough, in case a customer trusts developer code, he can also trust developer ad hoc queries instead of parameterized prepared statements, you should only not trust on anything external. Besides parameterization does not hinder XSS attacks or anything else executing what is stored in a database.

A high quality implicit conversion is one not depending on settings, eg with dates a 'YYYYMMDD' string is converted to the right date no matter whether the date setting is YMD or not. Also no matter if separators are slash, hyphen or dots.

In this case of comparing int field to empty string I'd also rather expected an error and not an implicit conversion. The only thing you can do is avoid it, I don't see an option causing the server to react throwing an error instead of converting '' to 0.

No points please, just a side note. Finally the idea to introduce a STRICT setting to SQL Server is looked at and discussed here, if you like to get ideas on what to look out for, as SQL Server does not do so: http://www.sommarskog.se/strict_checks.html
I'd support the idea to introduce such a feature to MSSQL, just to stress it out once more: This is NOT a setting you can actually do, just ideas on what could be done. Implicit conversions of strings to ints are a topic in there, too.

Bye, Olaf.

Thanks, Olaf.

Side note, I love your "Bye, Olaf" signature :)

Thanks, Camilia,

this signature has it's root as counterpoint to all those lengthy signatures people used to have and sometimes still have with all kinds of pointers to sites and other stuff.

Here at EE you can put all such info into a profile, so no need for such signatures anyway, but it got a habit.

Bye, Olaf.