Why SQL Server converts '' to zero

Question

Post reply

Why SQL Server converts '' to zero

patrickbrewer

Newbie

Points: 9
More actions
July 25, 2013 at 7:50 am

#395200

I have a web developer who sent me a query he couldn't figure out. Select Name from dbo.Employee where EmployeeID = ''. EmployeeID is an integer. First, I told him he shouldn't use '' as an integer. But, I could not explain why SQL Server converted '' to a zero. Can anyone answer that for me?
My assumption is that the engine sees a blank value as a zero because the column being compared to is an integer type. Since the value is nothing (not a null), the engine converts it to a zero because mathmatically zero added to anything doesn't change the value.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

This indeed returns 0:

SELECT CAST('' AS int)

It's just the way it works. I suppose when the DBMS or the standards were being written, somebody decided that '' would convert to 0 rather than throwing an error.

John

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

John Mitchell-245523 (7/25/2013)
This indeed returns 0:
SELECT CAST('' AS int)
It's just the way it works. I suppose when the DBMS or the standards were being written, somebody decided that '' would convert to 0 rather than throwing an error.
John

Yeah, empty string, when converted to any T-SQL numeric datatypes except DECIMAL and NUMERIC will end up as 0 (zero).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

I asked this same question a few months back and got some good replies; just thought I'd share this:

http://www.sqlservercentral.com/Forums/Topic1397471-391-1.aspx

Check this out:

SELECT CAST(REPLICATE(CAST('' AS int),10) AS char(10)) AS [10 0's]

SELECT 22+$ AS [$22.00]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001