Integer has implicit conversion?

This is SQL 2014.

I have a table with a column of integer.  I see zeros in it.

I did this and I get the rows with value of zero in that column

select * from loads 
where SettledWeight = 0 

Open in new window


I did this and I get the same values even tho that field is all zero. My coworker says it's because for an integer ‘’, blank, or null are 0.

Is this correct?

select * from loads 
where SettledWeight = ''

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
WOW! Good question and something you just don't think about, till you run across it.
SQL Server is implicitly converting the '' to a 0 based on Data Type Precedence.
https://msdn.microsoft.com/en-us/library/ms190309.aspx
Here is some test code to see what it is doing.
CREATE TABLE testloads (Id INT IDENTITY, SettledWeight INT, SomethingElse VARCHAR(50));
INSERT INTO dbo.testloads ( SettledWeight, SomethingElse )
SELECT * FROM (VALUES  ( 0, 'z' ), (NULL, 'y'), (' ', 'x'), ('', 'w')) x(W,V)

select * from testloads 
where SettledWeight = 0

select * from testloads 
where SettledWeight = ''

select * from testloads 
where SettledWeight IS NULL

DROP TABLE dbo.testloads;

Open in new window

Query Results
1
 
Brian PringleSystems Analyst II, SCM, ERPCommented:
You can specify the default by using the "DEFAULT" keyword when you created the column.  It is 0 by default, unless you change it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
. 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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
CamilliaAuthor Commented:
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.
0
 
Brian PringleConnect With a Mentor Systems Analyst II, SCM, ERPCommented:
Theoretically, they shouldn't...  However, because it is an integer data type, then the only valid values are whole numbers and null.  It will try to cast that '' string value to whatever is legal.  In this case, it casts it to 0.  If you don't want that to happen, the only option is to pick another data type.
0
 
Brian PringleSystems Analyst II, SCM, ERPCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
CamilliaAuthor Commented:
This still brought back values

 select * from loads
where cast (SettledWeight as int) = ''
0
 
CamilliaAuthor Commented:
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.
0
 
CamilliaAuthor Commented:
Thanks, Chris! this is great. let me take a look and run some tests.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
If data types don't match, SQL must implicitly (automatically) convert data so that the types match.  SQL uses rules of data precedence for this; you can find the complete list in Books Online.

When int and [var]char must be used together, both must first be converted to int.  Therefore:
SELECT '4' + 1
always yields 5 and never '41'.

When a blank is implicitly converted to int, it becomes 0.
SELECT CAST('' AS int)
An empty string is not part of the domain (valid values) for an integer column, thus it would be an invalid comparison.

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

No.  NULL is never equal to any other value (barring use of SET ANSI_NULLS OFF, which you should never use anyway).
.
1
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
2
 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
CamilliaAuthor Commented:
Thanks, guys. Let me read.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
1
 
Olaf DoschkeSoftware DeveloperCommented:
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.
1
 
CamilliaAuthor Commented:
Thanks, Olaf.

Side note, I love your "Bye, Olaf" signature :)
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.