SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Numeric or Not Numeric


Numeric or Not Numeric

Author
Message
Dietmar Weickert
Dietmar Weickert
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1674 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. Rolleyes

Best regards,
Dietmar Weickert.
alastors
alastors
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 40
in Argentina (and all Hispanic countries, AFAIK) comma separates the integral and decimal part, and the point separates the thousands
crashdan
crashdan
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1022 Visits: 757
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
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4342 Visits: 4408
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 :-)
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26053 Visits: 12499
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

Toby White
Toby White
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1563 Visits: 639
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
Kelsey Thornton
Kelsey Thornton
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 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
kaspencer
kaspencer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2667 Visits: 857
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.

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4342 Visits: 4408
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.

alastors
alastors
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 40
we just call it "coma" Tongue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search