Number Format Strings

Number Format Strings

Format strings are used as templates for converting a string into a number and a number into a string. The following elements may be combined in the format string fmt and used to generate a string from a number NumberToString or a number from a string StringToNumber.

Element Value
9 Each 9 represents a significant digit to be displayed. Leading zeros are displayed or treated as blanks. You must have enough significant digits specified to fit your data. For example, if your data can have up to five digits, then the format string has to have at least five sigificant digits.
0 Each 0 represents a significant digit. Leading zeros are displayed or treated as zeros.
$ Prefix: puts a $ (dollar sign) in front of the number. See the L element for other currency characters.
, (comma) Represents a comma in the numeric string. The comma is always interpreted as a group separator. See the G element for other group separator characters.
. (period) Represents a decimal point in the numeric string. See the D element for other decimal point characters.
MI Suffix: places a minus sign after the number if it is negative or a space if it is positive.
S Prefix or Suffix: Places a plus(+) sign in front of a positive number and a minus(-) sign in fornt of a negative number.
D Specifies the location of the decimal point. All format elements to the left of D will format the integer component of the value. All format elements to the right of D will format the fractional part of the value. Determination of the character used to represent the decimal character is platform specific. For MapInfo.NET this value will be determined by the Culture setting of the current thread. For Spatialware or Envinsa, this will be determined by the database (most likely an initialization parameter).
G Specifies the location of the group separator (e.g., the comma to separate thousands in 6,234). Like the decimal character D, determination of the character used to represent the group character is platform specific.
L Specifies the location of the local currency symbol (e.g., $). Like the decimal character D, determination of the character used to represent the group character is platform specific.

E

E-

E+

Indicates that the number is in scientific notation. Occurrences of 9 or 0 after the E element are interpreted as the exponent format characters. No occurrences of decimal point or grouping can appear after the E element. The E element may optionally be immediately followed by a plus (+) sign or a minus (-) sign. When explicitly specifying a plus (+) sign or a minus (-) sign after the E element, a positive exponent will specifically include the plus (+) sign and a negative exponent will display a minus (-) sign. If no sign is specified after the E element and the exponent is negative, the minus sign will be displayed in the first position after the E element. Thus, the number of 9 or 0 elements following the E element must be wide enough to accommodate a minus sign if the exponent is expected to be negative (otherwise an overflow will occur).

Examples using Number Format Strings

The following examples illustrate the use of the number format elements to format a number or parse a string. Results are shown with quotes to indicate the presence of blanks (spaces) in the result - the quotes themselves are not part of the result.


  NumberToString(123.4567, '999999.99')     : '   123.46'
  NumberToString(123.4567, '099999.99')     : '000123.46'
  NumberToString(1234567, '99,999,999')     : ' 1,234,567'
  NumberToString(1234567, '9,999,999,999')  : '    1,234,567'
  NumberToString(1234567, '0,999,999,999')  : '0,001,234,567'
  NumberToString(123.4567, '0.9999E99')     : '1.2346E 2'
  NumberToString(123.4567, '0.9999E09')     : '1.2346E02'
  NumberToString(123.4567, '0.9999E+09')    : '1.2346E+02'
  NumberToString(12.345, '9.99')            : '#.##'
  StringToNumber('123456', '99999')         : Error
  StringToNumber(' 123.456', '9999.999')    : 123.456
  StringToNumber(' 123.456-', '9999.999')   : Error
  StringToNumber(' 123.456-', '9999.999MI') : -123.456
  StringToNumber('1,234.56', '999,999.99')  : 1234.56
  StringToNumber('1,234.56', '999,999.9')   : 1234.5