T662, “Underscores in numeric literals”

1. Wrong results in cast
2. Only in literals, not in cast
3. Limited support in cast
4. Also for binary, octal and hexadecimal notation

The SQL standard allows implementations to ignore underscore characters (_) inside numbers that are written directly into the SQL statement (literals). This can be used to improve the readability of numbers.

SELECT 1_000_000
     , 0.000_001
     , 1_2.3_4e5_6
  FROM …

Underscores are allowed in all types of numeric literals: Integers, decimals and in the exponential (“scientific”) notation. If the system supports binary, octal and hexadecimal numbers, then the underscore is also allowed there. However, underscores are only allowed between digits (of the respective numeral system) and there is only one underscore allowed between two digits. Otherwise, they may appear everywhere—they are not restricted to groups of three digits.

The literal syntax of SQL is also used when casting a character string to numeric types.0 Consequently, underscores are also allowed in expressions such as cast('1_000' as integer).

1. Doesn’t fail, but returns the wrong result. E.g. just the part before the first underscore

Related

Optional Features

• T661, “Non-decimal integer literals”

Normative References

The underscore (_) as grouping separator in numbers is defined in ISO/IEC 9075-2:2023 as the optional feature T662, “Underscores in numeric literals”. It first appeared in the 2023 edition.