SQL Server: What locale should be used to format numeric values into SQL Server format?

Question

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

Answer 1

The SQL Server specifications for constants are describing the acceptable formats in T-SQL expressions and batches:

integer constants are represented by a string of numbers that are not enclosed in quotation marks and do not contain decimal points. Integer constants must be whole numbers; they cannot contain decimals.

decimal constants are represented by a string of numbers that are not enclosed in quotation marks and contain a decimal point.

float and real constants are represented by using scientific notation.

money constants are represented as string of numbers with an optional decimal point and an optional currency symbol as a prefix. Money constants are not enclosed in quotation marks. SQL Server does not enforce any kind of grouping rules such as inserting a comma (,) every three digits in strings that represent money. Commas are ignored anywhere in the specified money literal.

To indicate whether a number is positive or negative, apply the + or - unary operators to a numeric constant. This creates a numeric expression that represents the signed numeric value. Numeric constants use positive when the + or - unary operators are not applied.

The good news is that client applications don't need to worry about these requirements. Client applications should pass numeric values as @parameters, not as T-SQL literal constants.

Answer 2

the bad news is that ado.net put comma in generated proc for float sqlparameter, im getting crazy, i tried change culture but dont work