TO_NUMBER

Parse a string into a DECIMAL value according to a format pattern.

Category: numericReturns: DECIMALDialect: PostgreSql

Syntax

TO_NUMBER(str, fmt)

Description

## Overview Parses the input string into a DECIMAL value using the specified format pattern. TO_NUMBER is the inverse of TO_CHAR for numeric formats and is the preferred primitive for importing locale-formatted numeric strings from flat files, CSV extracts, and other textual sources. The format pattern is template-based: each character in the pattern describes the expected role of the corresponding character in the input. Common patterns cover digit counts, decimal separators, group separators, explicit signs, and trailing-sign conventions. ## Behavior - Accepts two STRING arguments; both are required. - Returns a DECIMAL with scale determined by the format pattern. - Returns NULL if either argument is NULL. - Raises a runtime error if the input does not match the format (for example, extra non-digit characters, wrong decimal separator). - The '9' token allows blank-padded digits; the '0' token requires the digit to be present. - Group separators ('G' or ',') are positional markers, not mandatory: TO_NUMBER('1234', '9,999,999') parses correctly. ## Numeric precision - The scale of the returned DECIMAL is taken from the format pattern. A format of '9.99' returns DECIMAL with scale 2; excess digits in the input that exceed the pattern raise an error. - The result is exact within the declared DECIMAL precision. ## Compatibility - Matches the PostgreSQL TO_NUMBER function. The format tokens are a subset of the traditional numeric format language shared across many SQL dialects.

Parameters

Name	Type	Description
`str`		Specifies the string to parse. Must conform to the layout described by the format pattern; leading and trailing whitespace is typically ignored.
`fmt`		Specifies the format pattern. Common tokens: '9' (digit, blank padded), '0' (digit, zero padded), 'D' or '.' (decimal point), 'G' or ',' (group separator), 'S' (sign), 'MI' (trailing minus), 'PR' (angle-bracketed negative).

Examples

-- Simple integer conversion
SELECT TO_NUMBER('42', '99');  -- 42

-- Decimal conversion
SELECT TO_NUMBER('3.14', '9.99');  -- 3.14

-- Thousands separator
SELECT TO_NUMBER('1,234,567', '9,999,999');  -- 1234567

-- Leading zeros in format
SELECT TO_NUMBER('007', '000');  -- 7

-- Negative with explicit sign
SELECT TO_NUMBER('-99.5', 'S99.9');  -- -99.5

-- NULL propagation
SELECT TO_NUMBER(NULL, '999');  -- NULL

Pitfalls

TO_NUMBER is strict about format conformance. An input '3.14' against format '9' raises an error because the decimal point is not in the pattern. Always match the decimal separator ('D' or '.') explicitly.
Locale-specific separators are hard-coded to the pattern, not to the session locale. If your data mixes locales ('1,234.56' and '1.234,56'), preprocess before calling TO_NUMBER.
The 'S' token placement matters: 'S99.9' expects a leading sign, 'MI' placed at the end expects a trailing minus. Check the token position in your format pattern when porting from another dialect.
Excess fractional digits in the input beyond the format's scale raise an error. Truncate or round the input upstream if your source has more precision than your target pattern.
TO_NUMBER does not interpret scientific notation ('1.5e3') out of the box. Strip the exponent or use a DOUBLE cast path for scientific-format data.