Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

''=0 ?!?! Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 4:26 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 586, Visits: 2,736
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. .

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...

Is this a bug, is my server haunted? Anyone know anything about this?


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1397471
Posted Monday, December 17, 2012 4:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 39,953, Visits: 36,309
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 2008, MVP
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

Post #1397473
Posted Monday, December 17, 2012 5:08 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 586, Visits: 2,736
Thanks Gail.

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1397479
Posted Monday, December 17, 2012 6:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 3,420, Visits: 5,344
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1397494
Posted Tuesday, December 18, 2012 8:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 586, Visits: 2,736
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+$)

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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1397866
Posted Tuesday, December 18, 2012 10:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 2,120, Visits: 3,202
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1397897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse