Tue 24 Nov 2009
T-SQL: IsNumeric function
Posted by Ralf under SQL Server
[2] Comments
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
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