Wise man or Wise guy? You Decide

Just another WordPress.com weblog

The Deception of IsNumeric()

Posted by sqlwiseguy on April 14, 2009

Has anyone else ever used the IsNumeric() function to try to eliminate this error (or similar):

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

yet still get the error? Isn’t that what the function is for?  Here’s what is says in BOL:

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0

But run this code (generates a list of ASCII Characters):

;WITH cteNumbers AS
   
(
   
SELECT TOP 255
        ROW_NUMBER
() OVER(ORDER BY NAME) AS n
   
FROM
       
sys.all_columns AS AC
   
)
   
SELECT
       
n AS ASCII_code,
       
CHAR(n) AS [character],
       
ISNUMERIC(CHAR(n)) AS is_numeric
   
FROM
       
cteNumbers
   
WHERE
       
n <=255 AND
       
ISNUMERIC(CHAR(n)) = 1

And you’ll see that the first 10 rows and last 8 rows returned are not what I would consider numeric.  Or how about this:

SELECT
   
ISNUMERIC('20,00')
GO
SELECT
   
CONVERT(DECIMAL(9,2), '20,00')

The first statement returns 1, while the second fails. 

Here is the last interesting behavior of IsNumeric() in relation to Cast/Convert provided by Frank Kalis on this SQLServerCentral thread:

SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0fAS F

E           F          
----------- -----------
0           0

(1 row(s) affected)

WHILE

SELECT CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F

E           F          
----------- -----------
14          15

The moral of the story is that IsNumeric() <> CanBeConvertedToNumeric().

So what is the answer?  I don’t know.  You would need to customize the solution to meet your specific situation.  Jeff Moden suggests an IsAllDigits function in this thread on SQLServerCentral for instances where you want to eliminate rows with non-numeric characters.

Do you have anything that you recommend?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: