The IsNumeric function has a very variable interpretation algorithm. In order to get only numeric values following statement should be used:
CASE WHEN (NUM_VALUE LIKE '%[0-9]%' AND ISNUMERIC(NUM_VALUE) = 1) THEN CAST(NUM_VALUE AS FLOAT) ELSE NULL END AS NUM_VALUE
Very good post, really informative. What about a blog post on SQL Jobs? It took me months to find a decent job in SQL. I found this site to to be quite useful in the end: SQL Jobs
Thank you for this hint.
Ralf,
yes, the ISNUMERIC() function is not really usefull. But the problem is slightly more complex. Here are some examples where your solution fails:
‘.,0′ => error
’0×12′ => is numeric, but returns NULL
‘$.0′ => error
‘-€,2′ => error
Regards,
Holger