What is a correct ISNUMERIC('.') value?

I am a little confused here. SELECT ISNUMERIC('.') returns 1, that is '.' string is considered to be of a numeric convertible type. However, SELECT CONVERT(numeric, '.') returns error message: "Error converting data type varchar to numeric." Looks like a controvercy to me... I would say that a correct ISNUMERIC('.') value should be 0 (zero). What do you think?

more ▼

asked Sep 09, 2010 at 11:50 AM in Default

avatar image

413 15 17 22

@Tatyana @Matt Whitfield Matt, could you please help me with voting problem? I tried to upvote this question, but the system went bisserk and did not change the count. So I clicked to upvote again and the number went down. In order to undo the damage, I clicked to upvote one more time and now it shows -2. All I wanted was to upvote the question, that is all. Thank you :)

Sep 09, 2010 at 12:56 PM Oleg

OK, not it appears to be fixed.

Sep 09, 2010 at 12:57 PM Oleg
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

There is no controvercy here. The mere fact that isnumeric returns true does not guarantee that the value is converible to numeric. This is due to the isnumeric implementation, which calls for trying to check whether the input can be represented as a numeric value in any of the available settings. For example, the input 123.456,56 is not convertible to decimal number with en_US settings, but isnumeric for this input is 1 because it happens to be a valid number in any environment where dot is used as a thousand separator and comma as decimal point. Same thing applies to 123 456,56 (isnumeric is 1 because a space is used as a thousand separator in some environments). On the other hand, it is possible to get isnumeric = 0 while the input is convertible. For example

 select isnumeric(' ') is_numeric, cast(' ' as int) cast_as_int;
 -- returns 
 is_numeric  cast_as_int
 ----------- -----------
 0           0

Though isnumeric returns false for the above, it still can be cast as integer (but not as decimal, decimal still errors out). Here is another obscure example:

 declare @i varchar(3);
 set @i = 1234;
 select isnumeric(@i) is_numeric;
 -- returns
 -- why the input is not numeric? try to see what does it equal to
 select @i result;

Where did the * come from? Well, it came from internal implementation of the int data type, which returns * if the integer value does not fit into varchar variable :)

The bottom line: any cave man attempts to safely convert input values to decimal if such inputs passed isnumeric validation are failure prone.


more ▼

answered Sep 09, 2010 at 12:27 PM

avatar image

17.1k 3 7 28

Thank you. I think this is the answer! Now I just need to find a completely new workaround... :)

Sep 09, 2010 at 01:14 PM Tatyana

@Tatyana Can you elaborate a bit on your requirement? It looks like you are looking for a method to sanitize the inputs and if convertible to decimal then convert them. Is this correct?

Sep 09, 2010 at 01:29 PM Oleg

@Tatyanna, if this is the best answer, please click on the button that indicates that this is the accepted answer.

Sep 09, 2010 at 01:46 PM Mark

Sorry, could not find that "accepted answer" button, and thought that maybe somebody has to do this for me :) @Oleg, - yes, it's pretty much what I need to do. Almost sounds like you know a good solution... do you?

Sep 09, 2010 at 01:57 PM Tatyana

While I am not thrilled with it, the most universal solution I have found in practice is to wrap the convert in a try block.

Sep 09, 2010 at 02:47 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Just wanted to clarify the difference

ISNUMERIC - is a function that will return true if the expression can be converted to any numeric data type, including: int, bigint, tinyint, decimal, numeric, real, money, smallmoney, float.

This has been discussed in detail at

Confusingly there is a numeric data type called numeric which is simply a synonym for decimal.

 SELECT CONVERT(numeric, '.')

is functionally equivalent to

 SELECT CONVERT(decimal, '.')

which when written like that is clear why it fails with an error.

more ▼

answered Sep 10, 2010 at 01:50 AM

avatar image

Kev Riley ♦♦
64.1k 48 61 81

Thank you, I did not find that discussion myself, it is very informative! Now, I see what I need to do: SELECT CONVERT(numeric, CONVERT(money, '.')) - returns the zero I wanted so much.

Sep 10, 2010 at 05:41 AM Tatyana
(comments are locked)
10|1200 characters needed characters left

Kev Riley has a good idea using PATINDEX here too: Kev's Solution.

(The link was too long for a comment.)

more ▼

answered Sep 09, 2010 at 01:56 PM

avatar image

2.6k 24 27 31

(comments are locked)
10|1200 characters needed characters left

Since a '.' could be a decimal or a period in a sentence, it's hard to say. Maybe it should return NULL since it could be either.

I think it returns 1 so that errors (or false alarms) will not be thrown when evaluating numbers with decimals. I would side with 1 on that count.

p.s., '-' and '+' also return 1.

more ▼

answered Sep 09, 2010 at 12:12 PM

avatar image

2.6k 24 27 31

Oh, I did not know about '+' and '-'! Now I see that ',' also returns 1. Hm... it's not very convinient, since all of these values could not be converted into numeric ones, so errors are thrown anyway. Maybe, then, their convertion should return a zero value instead of an error message? Thank you for your insight and additional information.

Sep 09, 2010 at 12:32 PM Tatyana

@Tatyana, you're welcome. Oleg makes some good points too. But if you're wanting to check each character in a string to see if it is a number, then simply test with something like IN (0,1,2,3,4,5,6,7,8,9)

Sep 09, 2010 at 12:41 PM Mark

Thank you, Mark, it's a very helpful suggestion! Unfortunately, my problem is more complicated, I guess. It's a report based on hospital's lab results, and a user needs to select a value range for results... but in their result value field, there is everything from numerics to strings (like "positive") - and I don't know now how to separate them. I tried: CASE WHEN ISNUMERIC(obsValue) = 1 AND NOT obsValue = '.' THEN CONVERT(numeric, obsValue) ELSE 0.0 END - but, it seems, I hardly can get away with just '.' ... Oh well... :) Thank you anyway, I've learned something new!

Sep 09, 2010 at 01:27 PM Tatyana
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 09, 2010 at 11:50 AM

Seen: 3952 times

Last Updated: Sep 09, 2010 at 11:50 AM

Copyright 2016 Redgate Software. Privacy Policy