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'!