''=0 ?!?!

Question

''=0 ?!?!

Alan Burstein

SSC Guru

Points: 61141
More actions
December 17, 2012 at 4:26 pm

#268347

This is something I ran into today and am curious if anyone knows anything about this....
I was working on something earlier and executed T-SQL command. I noticed a typo in my code after the query had executed successfully. The offending code looked something like this...
IF CHARINDEX('xxx','yy')=''
PRINT 'xxx'
Though logic would dictate otherwise, the above query runs successfully on 2008 & 2012. It appears that SQL Server treats double single quotes as a zero (0). I was curious so I ran these:
SELECT ISNUMERIC(5);
SELECT ISNUMERIC('');
The first statement returned a 1, the second returned a 0. This is what I expected: 5 is numeric, '' is not. :satisfied:.
Then I ran the following and it appears that ''=0...
SELECT 7*''
SELECT CAST('' AS int)
SELECT REPLICATE(CAST('' AS int),5)
After a bunch of Google searching I was not able to find anything about this... :blink:
Is this a bug, is my server haunted? Anyone know anything about this?
"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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL cannot compare two different data types, nor can it multiply by a string. Hence, any time you ask it to do that, it will do an implicit conversion. As you saw, casting '' to int results in 0 (it can't logically be anything else), so all you're seeing here is the implicit conversion of a string to an integer.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Thanks Gail.

"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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

If you think those "typos" that pass SQL Server's parser seem weird, try this one:

SELECT 7+$

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

dwain.c (12/17/2012)
If you think those "typos" that pass SQL Server's parser seem weird, try this one:
SELECT 7+$

Thanks for the post dwain. That's really odd too; I would never think to try that if not for this forum.. This was actually the first time I ever posted a question on any forumn - I decided to do so after you suggested it in a different thread recently (I wish I had a more interesting question to post).

I assumed that statement was casting 7 as money... I wanted to see for sure but couldn't think a way to do something like this:

SELECT GetTheDatatypeOf(7+$) :unsure:

Then I came up with:

select 7+$ AS dt

into tmp;

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='tmp' AND COLUMN_NAME='dt';

DROP TABLE tmp;

Edit: Typo

"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

ScottPletcher SSC Guru Points: 101101 More actions · Answer 5

Interesting too is that an empty string does not work for decimal values:

SELECT 1.1+''

But it again does work for dates:

SELECT CAST('' as datetime)

Ah the wonders of SQL -- "wonder why it does that?!"

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".