Ralf Eisenreich

SQLBlog.DE | ..things to remember

November 24th, 2009

T-SQL: IsNumeric function

SQL Server, by Ralf.

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

Back Top

Responses to “T-SQL: IsNumeric function”

Comments (3) Trackbacks (0) Leave a comment Trackback url
  1. 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

  2. Thank you for this hint.

  3. 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

  1. No trackbacks yet.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Security Code: