ISNUMERIC Issue

Question

ISNUMERIC Issue

apat

SSCertifiable

Points: 5929
More actions
July 26, 2010 at 8:38 am

#225387

Hi All,
select isnumeric('100,200') gives me 1.
Shouldn't it be 0?
Thanks...

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Uh, no...

100,200 is a real number. Just because it's in a character string doesn't change that. ISNUMERIC looks to see if whatever you pass it can be made into a number or not. Doesn't care about the data type of whatever is passed in.

apat SSCertifiable Points: 5929 More actions · Answer 2

there is a comma between 100 and 200, won't it cosider comma?

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

In the US at least, a comma is used every three places to make numbers easy to read. You passed in in essence "one hundred thousand, two hundred" as a number to the function.

apat SSCertifiable Points: 5929 More actions · Answer 4

apat

SSCertifiable

Points: 5929

July 26, 2010 at 9:04 am

#1198249

Thank you for the reply Jeff...

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

This should depend on your regional settings, but I wouldn't be surprised if all checks on this return 1.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

I think that regardless of settings such comma separated string in SQL can be easely converted to money:

select CAST ('100,200' as money)

returns 100200.00 !

and

select CAST ('100,200,300' as money)

returns 100200300.00

And money is NUMERIC! 😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

apat (7/26/2010)
select isnumeric('100,200') gives me 1.
Shouldn't it be 0?

No.

SELECT CAST('100,200' AS MONEY)

Result:

100200.00

IsNumeric returns true if the value can be case to one of more of the numeric data types. Which one is another story. 100,200 won't convert to numeric, int or float, but it will to Money. Therefore it's considered a numeric type.

Pain in the neck to work with, but that's how it is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 8

What do you know. I always knew that ISNUMERIC would work with commas but assumed it was because we used them when we write in the real world and SQL knew that. But I just tested out Gail's stuff and sure enough, you can't use commas on a straight numeric. Learn something new every day.

Nevyn SSChampion Points: 14210 More actions · Answer 9

The way "money" and thus "isnumeric" handles commas seems to be fairly dumb, though. Instead of validating commas in only certain placements, I guess they decided to just make commas transparent for the conversion.

For example, try select cast ('1,2.1,,,,3' as money)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

It would by handy if SQL Server had an enhanced IsNumeric function that you could pass a specific target data type to, but that's the way it is for now.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

Nevyn (7/26/2010)
The way "money" and thus "isnumeric" handles commas seems to be fairly dumb, though. Instead of validating commas in only certain placements, I guess they decided to just make commas transparent for the conversion.
For example, try select cast ('1,2.1,,,,3' as money)

I would not call it dumb. It is just flexible. SQL is "aware" that money are commonly formatted with commas, however no one guarantees that commas separate every three digits. You can format it as you wish (eg. #,##,####,#.##,#0). Therefore when converting such values into money it simply ingores commas...

check what it does with:

select CAST ('10,2,300.12,45' as money)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 12

Michael Valentine Jones (7/26/2010)
It would by handy if SQL Server had an enhanced IsNumeric function that you could pass a specific target data type to, but that's the way it is for now.

https://connect.microsoft.com/

:hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Nevyn (7/26/2010)
The way "money" and thus "isnumeric" handles commas seems to be fairly dumb, though.

Tell me about it.

There are days that I'd kill for an IsNumeric that returns true if I can cast to NUMERIC and false otherwise. Try converting a column with values like this to a numeric format

CREATE TABLE AllNumeric (

Value VARCHAR(12)

)

INSERT INTO AllNumeric (Value) Values ('123,456.789'), ('123.05E-02')

GO

SELECT Value, ISNUMERIC(Value)

FROM AllNumeric

Both consideren Numeric, one will go to Money, one to Float.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Steve Jones - Editor (7/26/2010)
Michael Valentine Jones (7/26/2010)
It would by handy if SQL Server had an enhanced IsNumeric function that you could pass a specific target data type to, but that's the way it is for now.
https://connect.microsoft.com/
:hehe:

It's up there. Multiple times.

https://connect.microsoft.com/SQLServer/feedback/details/302466/isnumeric-returns-true-for-and

https://connect.microsoft.com/SQLServer/feedback/details/177308/enhancement-to-isnumeric

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass