Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Numeric or Not Numeric Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 4:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, Visits: 374
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.



Best regards,
Dietmar Weickert.
Post #870238
Posted Monday, February 22, 2010 6:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 12:15 PM
Points: 33, Visits: 40
in Argentina (and all Hispanic countries, AFAIK) comma separates the integral and decimal part, and the point separates the thousands
Post #870278
Posted Monday, February 22, 2010 7:04 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 PM
Points: 782, Visits: 730
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
Post #870323
Posted Monday, February 22, 2010 7:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #870324
Posted Monday, February 22, 2010 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 7,688, Visits: 9,412
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
Post #870357
Posted Friday, February 26, 2010 7:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
Good question. I ran up against an issue in data before where it was not actaully a valid numeric value, but passed the isnumeric test and broke the process upon insert. The only thing I would add beyond the difference in formating between countries is that you could just have some plain bad data like the following that would pass the isnumeric test

DECLARE @PrincipalAmount VARCHAR(15)SET @PrincipalAmount = '7,0,0,,,0.0,0,,,'
SELECT ISNUMERIC(@PrincipalAmount)

Regards,

Toby
Post #873413
Posted Monday, March 1, 2010 11:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, Visits: 282
kaspencer (2/22/2010)

<snip>
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


<devils advocate>
Who's to say that the full-stop is more "logical"? Why not the colon or semicolon?
Why not, indeed, the comma?
</devils advocate>

(Living in the Netherlands, I see the comma as decimal separator all the time... )


Kelsey Thornton
MBCS CITP
Post #874881
Posted Tuesday, March 2, 2010 2:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:51 AM
Points: 1,743, Visits: 485
Ah, yes, maybe, Kelsey.

BUT if you are going to call it the "decimal point", then there is only only one punctuation symbol that resembles a point, and that is the full-stop. Surely?

Otherwise, then let's call it a decimal comma, a decimal exclamation mark, or even a decimal colon!

As for the thousands separator, the space is completely illogical, as, mechanistically, one doesn't know whether one is dealing with a list of numbers, or continuation of the previous number.

If ever there is a revolution, let's re-organise separators completely, as follows:

So that 100,678,830.56 could become 100T678T830D56.

Only joking!

Ken.
Post #874943
Posted Tuesday, March 2, 2010 2:55 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
kaspencer (3/2/2010)
Otherwise, then let's call it a decimal comma

Maybe you'll be surprised by the fact that the decimal separator IS called 'decimal comma' in many countries: http://en.wikipedia.org/wiki/Decimal_separator#Countries_using_Arabic_numerals_with_decimal_comma

Here's a quote from Wikipedia:
The decimal separator or decimal point or decimal comma is a symbol used to mark the boundary between the integral and the fractional parts of a decimal number in a positional numeral system.
Post #874951
Posted Tuesday, March 2, 2010 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 12:15 PM
Points: 33, Visits: 40
we just call it "coma" :P
Post #875176
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse