case when issue
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:
Software/Hardware used:
ASKED:
November 17, 2007 1:39 AM
UPDATED:
January 29, 2008 1:57 AM
Answer Wiki:
You are getting this because the data within the THEN portion of the CASE statement is a number. You would need to put a numeric value into the ELSE portion. Your other option would be to leave the THEN value as a text string.
A single column can only have a single data type. "convert(numeric, '-10.00%')" is a number, while 'Not Numeric' is a string.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
