April 12, 2010 at 1:57 pm
I found this out by chance today while trying to figure out the strange results of some T-SQL code on a couple of our servers.
CREATE TABLE test1
(
id int,
[name] varchar(20)
)
INSERT INTO test1(id,[name]) VALUES (1,'Mike')
INSERT INTO test1(id,[name]) VALUES (2,'Jeff')
INSERT INTO test1(id,[name]) VALUES ('','WhoamI')
INSERT INTO test1(id,[name]) VALUES (0,'Correct0')
SELECT * FROM test1
SELECT * FROM test1 WHERE id = 0;
SELECT * FROM test1 WHERE id = '';
SELECT * FROM test1 WHERE id = '0';
Now, the results of this are different from what I'd expect. I would have guessed that the 'whoamI' record would be rejected during insert for not being 'numeric' (Note: SELECT IsNumeric('') returns a 0...), and that the WHERE id = '' CLAUSE would break for similar reasons. This is not the case.
Thing is, here's where it really threw me:
TRUNCATE TABLE test1;
ALTER TABLE test1 ADD CONSTRAINT ck_id CHECK (id <> '')
GO
INSERT INTO test1(id,[name]) VALUES (1,'Mike')
INSERT INTO test1(id,[name]) VALUES (2,'Jeff')
INSERT INTO test1(id,[name]) VALUES ('','WhoamI')
INSERT INTO test1(id,[name]) VALUES (0,'Correct0')
SELECT * FROM test1
SELECT * FROM test1 WHERE id = 0;
SELECT * FROM test1 WHERE id = '';
SELECT * FROM test1 WHERE id = '0';
DROP TABLE test1
The last two records are not inserted. This (kinda sorta) implies " '' = '0' ", which is where my brain shuts down :-). Now, I'm sure there's a simple explanation for why this happens, but since I've never encountered the situation before I was hoping some of you guys might be able to explain what I'm missing here. Is there another way to look at this so the current behavior would make more sense to me?
Thanks in advance!
Regards,
Mike M
April 12, 2010 at 2:20 pm
Empty strings = 0. It's kind of arbitrary, but that's how it works.
IsNumeric has a number of flaws, this is just one of them. Check out IsNumeric('1d3') for a surprising result. It's valid, because it's an extended numeric shorthand. At the same time, Cast('1d3' as Int) will fail, but casting it to Float will succeed and give you 1000. Those are some of the reasons I don't trust IsNumeric. It does what it's supposed to, but what it's designed to do isn't helpful to me in any of the situations that I need it for.
On the last bit, casting '0' to an integer is an allowed implicit conversion, so that's why that is succeeding. Implicit conversions can be tricky at first, but once you get the hang of them, they're pretty obvious.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2010 at 2:22 pm
i thought it was one of those implicit conversion things;
when your WHERE is evaluated here:
id = '';
the id column is implicitly converted to varchar....since every int can convert to a varchar, there's no problem;
how that '' gets converted to zero...i dunno;
It's not the opposite, where the '' is converted to a number; there is an order of implicit conversions, and i've seen it somewhere, but won't stick my foot in my mouth guessing which goes to what.
Lowell
April 12, 2010 at 2:34 pm
GSquared (4/12/2010)
Empty strings = 0. It's kind of arbitrary, but that's how it works..
That's what I was hoping wouldn't be the answer, but was expecting it anyway. 😉
IsNumeric has a number of flaws, this is just one of them.
I've seen you guys rip into it in the past. Jeff M recently posted a list in another forum that made me chuckle.
On the last bit, casting '0' to an integer is an allowed implicit conversion, so that's why that is succeeding. Implicit conversions can be tricky at first, but once you get the hang of them, they're pretty obvious.
Yeah, *normally* I'm good on implicit conversions. This ('' = 0) and ('' = '0') thing was completely new to me. I've never seen it in 10 years of SQL coding. I put the above test together to confirm that I wasn't crazy.
Thanks for the input!
Regards,
Mike M
April 12, 2010 at 4:41 pm
GSquared (4/12/2010)
Empty strings = 0. It's kind of arbitrary, but that's how it works.IsNumeric has a number of flaws, this is just one of them. Check out IsNumeric('1d3') for a surprising result. It's valid, because it's an extended numeric shorthand. At the same time, Cast('1d3' as Int) will fail, but casting it to Float will succeed and give you 1000. Those are some of the reasons I don't trust IsNumeric. It does what it's supposed to, but what it's designed to do isn't helpful to me in any of the situations that I need it for.
On the last bit, casting '0' to an integer is an allowed implicit conversion, so that's why that is succeeding. Implicit conversions can be tricky at first, but once you get the hang of them, they're pretty obvious.
Actually, none of that is a "flaw" per se`. IsNumeric handles those just like it's supposed to. Since the value (a form of engineering notation, in the case of '1d3') can be successfully converted to a "numeric" value using some method, it will dutifully report a "1" for such values just like it'll report a "1" for the following...
SELECT ISNUMERIC('1,1,1,1,1,1,1.1'),
ISNUMERIC(CHAR(9)+'1')
The problem is that people mistake the term "IsNumeric" for "IsAllDigits" and the two just aren't the same. 🙂 From Books Online...
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. [font="Arial Black"]A return value of 1 indicates that expression can be converted to at least one of the numeric types[/font].
No where does it make the qualification of being an "IsAllDigits" function. It will take anything that can be converted to a number using one method or another including signage, a couple of forms of engineering notation, currency marks, currency characters, tabs, spaces, CrLf's, hard spaces, and a whole bunch more and report it as a "1".
--Jeff Moden
April 14, 2010 at 6:30 am
Jeff: I understand that IsNumeric does what it's designed to do, but I still consider it "flawed" because it has almost no practical use in my experience. That's why I specifically stated, "It does what it's supposed to, but what it's designed to do isn't helpful to me in any of the situations that I need it for." It's not buggy, but it is, in my opinion, flawed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply