Number Patterns for Formatting and Parsing

Description

Functions such as to_number and to_char support converting between values of string and Decimal type. Such functions accept format strings indicating how to map between these types.

Syntax

Number format strings support the following syntax:

  { ' [ MI | S ] [ $ ] 
      [ 0 | 9 | G | , ] [...] 
      [ . | D ] 
      [ 0 | 9 ] [...] 
      [ $ ] [ PR | MI | S ] ' }

Elements

Each number format string can contain the following elements (case insensitive):

Function types and error handling

Examples

The following examples use the to_number, try_to_number, and to_char SQL functions.

Note that the format string used in most of these examples expects:

The to_number function

-- The negative number with currency symbol maps to characters in the format string.
> SELECT to_number('-$12,345.67', 'S$999,099.99');
  -12345.67
 
-- The '$' sign is not optional.
> SELECT to_number('5', '$9');
  Error: the input string does not match the given number format
 
-- The plus sign is optional, and so are fractional digits.
> SELECT to_number('$345', 'S$999,099.99');
  345.00
 
-- The format requires at least three digits.
> SELECT to_number('$45', 'S$999,099.99');
  Error: the input string does not match the given number format
 
-- The format requires at least three digits.
> SELECT to_number('$045', 'S$999,099.99');
  45.00
 
-- MI indicates an optional minus sign at the beginning or end of the input string.
> SELECT to_number('1234-', '999999MI');
  -1234
 
-- PR indicates optional wrapping angel brackets.
> SELECT to_number('9', '999PR')
  9

The try_to_number function:

-- The '$' sign is not optional.
> SELECT try_to_number('5', '$9');
  NULL
 
-- The format requires at least three digits.
> SELECT try_to_number('$45', 'S$999,099.99');
  NULL

The to_char function:

> SELECT to_char(decimal(454), '999');
  "454"

-- '99' can format digit sequence with a smaller size.
> SELECT to_char(decimal(1), '99.9');
  " 1.0"

-- '000' left-pads 0 for digit sequence with a smaller size.
> SELECT to_char(decimal(45.1), '000.00');
  "045.10"

> SELECT to_char(decimal(12454), '99,999');
  "12,454"

-- digit sequence with a larger size leads to '#' sequence.
> SELECT to_char(decimal(78.12), '$9.99');
  "$#.##"

-- 'S' can be at the end.
> SELECT to_char(decimal(-12454.8), '99,999.9S');
  "12,454.8-"

> SELECT to_char(decimal(12454.8), 'L99,999.9');
  Error: cannot resolve 'to_char(Decimal(12454.8), 'L99,999.9')' due to data type mismatch:
  Unexpected character 'L' found in the format string 'L99,999.9'; the structure of the format
  string must match: [MI|S] [$] [0|9|G|,]* [.|D] [0|9]* [$] [PR|MI|S]; line 1 pos 25