I recently came across behavior of a "case when" conditional expression that really confuses me. In this case:
--
CASE when isnumeric('-10.00%') > 0 then convert(numeric, '-10.00%') end
----
the behavior is as I expected - the isnumeric() function returns 0 (cuz it ain't no number) so the "then" is not evaluated and a null is returned. HOWEVER, if you simply add an else to the case when expression then there is a failed varchar to numeric conversion. So...
----
CASE when isnumeric('-10.00%') > 0 then convert(numeric, '-10.00%') else 'Not Numeric' end
----
bombs out with error message "Error converting data type varchar to numeric."
Hmmm, that sure does confuse me! If anyone knows what is wrong with my understanding of the behavior I would be ever-so-grateful for an explanation.
Thank you kindly, this one has me reelin'!
zh
Software/Hardware used:
ASKED:
November 17, 2007 1:39 AM
UPDATED:
January 29, 2008 1:57 AM