SQL Server: What locale should be used to format numeric values into SQL Server format?
- by Ian Boyd
It seems that SQL Server does not accept numbers formatted using any particular locale.
It also doesn't support locales that have digits other than 0-9.
For example, if the current locale is bengali, then the number 123456789 would come out as "?????????". And that's just the digits, nevermind what the digit grouping would be.
But the same problem happens for numbers in the Invariant locale, which formats numbers as "123,456,789", which SQL Server won't accept.
Is there a culture that matches what SQL Server accepts for numeric values? Or will i have to create some custom "sql server" culture, generating rules for that culture myself from lower level formatting routines?
If i was in .NET (which i'm not), i could peruse the Standard Numeric Format strings. Of the format codes available in .NET:
c (Currency): $123.46
d (Decimal): 1234
e (Exponentional): 1.052033E+003
f (Fixed Point): 1234.57
g (General): 123.456
n (Number): 1,234.57
p (Percent): 100.00 %
r (Round Trip): 123456789.12345678
x (Hexadecimal): FF
Only 6 accept all numeric types:
c (Currency): $123.46
d (Decimal): 1234
e (Exponentional): 1.052033E+003
f (Fixed Point): 1234.57
g (General): 123.456
n (Number): 1,234.57
p (Percent): 100.00 %
r (Round Trip): 123456789.12345678
x (Hexadecimal): FF
And of those only 2 generate string representations, in the en-US locale anyway, that would be accepted by SQL Server:
c (Currency): $123.46
d (Decimal): 1234
e (Exponentional): 1.052033E+003
f (Fixed Point): 1234.57
g (General): 123.456
n (Number): 1,234.57
p (Percent): 100.00 %
r (Round Trip): 123456789.12345678
x (Hexadecimal): FF
Of the remaining two, fixed is dependant on the locale's digits, rather than the number being used, leaving General g format:
c (Currency): $123.46
d (Decimal): 1234
e (Exponentional): 1.052033E+003
f (Fixed Point): 1234.57
g (General): 123.456
n (Number): 1,234.57
p (Percent): 100.00 %
r (Round Trip): 123456789.12345678
x (Hexadecimal): FF
And i can't even say for certain that the g format won't add digit groupings (e.g. 1,234).
Is there a locale that formats numbers in the way SQL Server expects? Is there a .NET format code? A java format code? A Delphi format code? A VB format code? A stdio format code?
latin-numeral-digits
