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'!
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!