TO_NUMBER - Amazon Redshift
SyntaxArgumentsReturn typeExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

TO_NUMBER

TO_NUMBER converts a string to a numeric (decimal) value.

Note

We recommend that you use FM in your format string to suppress padding blanks and zeroes. For a list of valid formats, see Numeric format strings.

Syntax

to_number(string, format)

Arguments

string

String to be converted. The format must be a literal value.

format

The second argument is a format string that indicates how the character string should be parsed to create the numeric value. For example, the format 'FM99D999' specifies that the string to be converted consists of five digits with the decimal point in the third position. For example, to_number('12.345','FM99D999') returns 12.345 as a numeric value. For a list of valid formats, see Numeric format strings.

Return type

TO_NUMBER returns a DECIMAL number.

If the conversion to format fails, then an error is returned.

Examples

The following example converts the string 12,454.8- to a number: 

select to_number('12,454.8-', 'FM99G999D9S');

to_number
-----------
-12454.8

The following example converts the string $ 12,454.88 to a number: 

select to_number('$ 12,454.88', 'FML99G999D99');

to_number
-----------
12454.88

The following example converts the string $ 2,012,454.88 to a number: 

select to_number('$ 2,012,454.88', 'FML9,999,999.99');

to_number
-----------
2012454.88