x

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

Tatyana gravatar image

Tatyana
413 15 15 18

@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
is_numeric
-----------
0

-- why the input is not numeric? try to see what does it equal to
select @i result;

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

<!-- Begin Edit

Here is the sample of such attempt. I will create a table, populate it with few records and then try to sanitize the data and then select it. Possibilities are endless and never bullet proof though :(

create table dbo.DecimalSample 
(
    id int identity(1, 1) not null
       constraint PK_DecimalSample_ID primary key clustered,
    test_result varchar(50) null
);
go

insert into dbo.DecimalSample
select null test_result
union select ' '
union select '.'
union select '123 456,56.77'
union select 'close to 456,789, but I am not sure'
union select 'positive'
union select '.67321'
union select '0.2323232'
union select '5.2'
union select '123,777'
union select ' 15   ';

-- cave man attempt to make it work assuming en-us settings
-- whatever is not convertible is replaced with 0.0000 :)
;with records(id, test_result, sanitized) as
(
    select
        id, test_result, 
        rtrim(ltrim(replace(replace(test_result, ' ', ''), ',', ''))) 
        as sanitized
        from dbo.DecimalSample
)
    select 
        id, test_result,
        case 
            when isnumeric(sanitized) = 0 or sanitized in ('.', '') then 0
            else cast(sanitized as decimal(19, 4))
        end converted
        from records;

-- the above returns:
id          test_result                         converted
----------- ----------------------------------- --------------
1           NULL                                0.0000
2                                               0.0000
3            15                                 15.0000
4           .                                   0.0000
5           .67321                              0.6732
6           0.2323232                           0.2323
7           123 456,56.77                       12345656.7700
8           123,777                             123777.0000
9           5.2                                 5.2000
10          close to 456,789, but I am not sure 0.0000
11          positive                            0.0000

One more thing I would like to point out. Cast and Convert to decimal without specifying precision and scale is not a good idea because default scale is 0 meaning that decimal numbers are going to be converted incorrectly. For example, select convert(decimal, '123.4567') returns 123 and this is probably not what is expected. select convert(decimal(10,2), '123.4567') returns 123.46 with just a small rounding error and select convert(decimal(10,2), '123.4567') returns 123.4567 not loosing any precision at all.

End Edit -->

Oleg
more ▼

answered Sep 09, 2010 at 12:27 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

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

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

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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][1].

(The link was too long for a comment.)

[1]: http://ask.sqlservercentral.com/questions/608/how-to-select-records-with-letters-in-an-nvarchar-field-that-should-only-be-numbers/613
more ▼

answered Sep 09, 2010 at 01:56 PM

Mark gravatar image

Mark
2.6k 23 25 27

(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

Mark gravatar image

Mark
2.6k 23 25 27

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x986
x21

asked: Sep 09, 2010 at 11:50 AM

Seen: 2653 times

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