Numeric or Not Numeric

Question

Numeric or Not Numeric

AmolNaik

SSCarpal Tunnel

Points: 4767
More actions
February 20, 2010 at 1:23 pm

#215876

Comments posted to this topic are about the item Numeric or Not Numeric
Amol Naik
February 21, 2010 at 11:19 pm

This was removed by the editor as SPAM

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

nice question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

David Todd-242471 Right there with Babe Points: 797 More actions · Answer 2

Hi,

Interesting question.

But this code - based on the question - fails with Error converting data type varchar to float.

[font="Courier New"]if( select isnumeric( @PrincipalAmount )) = 1

select convert( float, @PrincipalAmount )

else

print 'Sorry'

;[/font]

That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to. It would be better imho if isnumeric was more robust. What is the point of using it, if will have to use exception handling anyway to catch errors that isnumeric missed?

Cheers

David

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

ISNUMERIC() function always ignores commas while validating data

The question is good, but the explanation is wrong. In some cases commas are not ignored:

SELECT ISNUMERIC('-1') -- the result is 1

SELECT ISNUMERIC(',-1') -- the result is 0, this shows that the comma is not ignored

Another example:

SELECT ISNUMERIC('') -- the result is 0

SELECT ISNUMERIC(',') -- the result is 1; if ISNUMERIC() ignored the comma,

-- the result would be 0

David Todd-242471 (2/21/2010)
But this code - based on the question - fails with Error converting data type varchar to float.
if( select isnumeric( @PrincipalAmount )) = 1
select convert( float, @PrincipalAmount )
else
print 'Sorry'
;
That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to.

Float is not the only numeric data type 🙂 The conversion to money works fine.

ziangij SSCrazy Eights Points: 8066 More actions · Answer 4

That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to
Float is not the only numeric data type 🙂 The conversion to money works fine.

conversion to money alone works... nothing else viz. float, int, numeric works...

Fal Hall of Fame Points: 3143 More actions · Answer 5

ISNUMERIC is more general than CONVERT. The following is also valid (in 2008):

SET @PrincipalAmount = '100e2'

It will convert to float, but not money. With the QotD I was more concerned about the decimal place with no trailing zeroes.

S.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

We already had a similar question before.

If a number string can be converted to any numeric type, ISNUMERIC returns 1.

Among the known "issues" with this are:

money: 1,,,,,,1 is a valid money value (obviously it does not make sense though)

float: 1e1 is a valid float value

float: 1d1 is a valid float value

IsNumeric is consistent in the way that it verifies that it can convert to any SQL Server numeric data type (which includes monetary datatypes & float).

So blame it on the conversion functions instead:)

Best Regards,

Chris Büttner

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 7

Good question. And it also raises a further point in my mind ...

Is there a need to standardise numerical representation across every locale ?

I ask this because, in some locales, the number expressed as

700,000

could represent

700 [decimal point] 000

that is, 700 expressed to three decimal places.

In other locales, it would be

700 [thousands separator] 000

I have noticed a mixture of decimal point indicators on invoices these days, and it certainly isn't unusual to get one using the comma [,] as a decimal point rather than the more logical full-stop [.].

Kenneth Spencer

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

RedLlewy Ten Centuries Points: 1084 More actions · Answer 8

Good Question:

Also

DECLARE @PrincipalAmount VARCHAR(15)

SET @PrincipalAmount = '£700,000'

SELECT ISNUMERIC(@PrincipalAmount)

returns 1 as does

DECLARE @PrincipalAmount VARCHAR(15)

SET @PrincipalAmount = '$700,000'

SELECT ISNUMERIC(@PrincipalAmount)

See http://msdn.microsoft.com/en-us/library/ms188688.aspx for other recognised currency symbols

RedLlewy
"The Plural of Anecdote is not Data"

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 9

kaspencer (2/22/2010)
Good question. And it also raises a further point in my mind ...
Is there a need to standardise numerical representation across every locale ?
I ask this because, in some locales, the number expressed as
700,000
could represent
700 [decimal point] 000
that is, 700 expressed to three decimal places.
In other locales, it would be
700 [thousands separator] 000
I have noticed a mixture of decimal point indicators on invoices these days, and it certainly isn't unusual to get one using the comma [,] as a decimal point rather than the more logical full-stop [.].
Kenneth Spencer

You are right, in German the comma separates integral and decimal part, and the point separates the thousands.

Makes both formatting and scanning numbers real fun. At least Windows helps, as it covers the problem in its Regional and Language Settings in the Control Panel, and most libraries take these settings into account.

Currency signs, first day of the week, the sequence of day month and year in a standard date and other often neglected differences may become a pain in the ... as well. :rolleyes:

Best regards,
Dietmar Weickert.

alastors SSC Enthusiast Points: 157 More actions · Answer 10

in Argentina (and all Hispanic countries, AFAIK) comma separates the integral and decimal part, and the point separates the thousands

crashdan SSCommitted Points: 1990 More actions · Answer 11

Fal (2/22/2010)
ISNUMERIC is more general than CONVERT. The following is also valid (in 2008):
SET @PrincipalAmount = '100e2'
It will convert to float, but not money. With the QotD I was more concerned about the decimal place with no trailing zeroes.
S.

i found out the "e2" issue by accident. One of our customers likes using those in conjunction with numbers for locations ... WTH

vk-kirov SSCertifiable Points: 7686 More actions · Answer 12

kaspencer (2/22/2010)
Good question. And it also raises a further point in my mind ...
Is there a need to standardise numerical representation across every locale ?

Is there a need to standardize systems of measurement, alphabets, languages etc? This is what makes countries and cultures different from each other 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 13

A nice question.

It's pretty appalling that 8% of people chose the "error message" answer, though.

I'm much less surprised about the 17% who got it wrong to date by picking 0, as it seems (until you think about it properly) reasonable to assume that isNumeric is locale dependent and should return 0 in locales where the representation, with fraction and thousands seperators, of 700000 is '700.000,'. In fact it isn't locale dependent - whichever way round you use ',' and '.' it is acceptable to isNumeric in all locales - and also the conversion functions accept both notations instead of just the one applicable in the current locale and it wouldn't in fact be reasonable to do make it locale dependent. On the contrary it would be unreasonable and significantly reduce usability - for example people in the UK need to be able to process electronic invoices from UK, Ireland and the US and also from mainland Europe - so two diffent separator conventions have to be supported.

Tom