> This page location: String Functions > TO_NUMBER > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL TO_NUMBER() Function **Summary**: in this tutorial, you will learn how to use the PostgreSQL `TO_NUMBER()` function to convert a [character string](../postgresql-tutorial/postgresql-char-varchar-text) to a [numeric](../postgresql-tutorial/postgresql-numeric) value according to a specified format. ## Introduction to the PostgreSQL TO_NUMBER() function The PostgreSQL `TO_NUMBER()` function allows you to convert a string to a number based on a specified format. Here's the basic syntax of the `TO_NUMBER()` function: ```sql TO_NUMBER(string, format) ``` The `TO_NUMBER()` function requires two arguments: - string: This is a string that you want to convert to a number. - format: This is the format that specifies how the string should be interpreted as a number. The `TO_NUMBER()` function returns a value whose data type is numeric. The following table illustrates the list of valid formats: | Format | Description | | ---------- | --------------------------------------------------------------------------------------------------- | | 9 | Numeric value with the specified number of digits | | 0 | Numeric value with leading zeros | | . (period) | decimal point | | D | Sign anchored to a number that uses the locale | | , (comma) | group (thousand) separator | | FM | Fill mode, which suppresses padding blanks and leading zeroes. | | PR | Negative value in angle brackets. | | S | Sign anchored to a number that uses locale | | L | Currency symbol that uses locale | | G | Group separator that uses locale | | MI | Minus sign in the specified position for numbers that are less than 0. | | PL | Plus sign in the specified position for numbers that are greater than 0. | | SG | Plus / minus sign in the specified position | | RN | Roman numeral ranges from 1 to 3999 – currently, it **does not work** for the Roman numeric string. | | TH or th | Upper case or lower case ordinal number suffix | Noted that these format strings are also applicable to [`TO_CHAR()`](https://neon.com/postgresql/postgresql-string-functions/postgresql-to_char) function. ## PostgreSQL TO_NUMBER() function examples Let's take a look at some examples of using the `TO_NUMBER()` function to understand how it works. ### 1) Converting a string to a number The following example uses the `TO_NUMBER()` function to convert the string `'12,345.6-'` to a number. ```sql SELECT TO_NUMBER( '12,345.6-', '99G999D9S' ); ``` The output is: ```text to_number ----------- -12345.6 (1 row) ``` In this example: - `'12,345.6-'` is the input that we want to convert to a number. The input string consists of a group separator (`,`), a decimal point (`.`), and a minus sign (`-`) indicating a negative number. - `'99G999D9S'` is the format pattern used to interpret the input string. Each character in the format pattern has a specific meaning: - `9`: A digit placeholder. - `G`: The group separator (`,`). - `D`: The decimal point (`.`). - `S`: The sign (either `+` or `-`). The TO_NUMBER() parses the input string `'12,345.6-'` according to the format `'99G999D9S'` and returns a numeric value `-12345.6` ### 2) Converting a money amount to a number The following example uses the `TO_NUMBER()` function to convert a money amount to a number: ```sql SELECT TO_NUMBER( '$1,234,567.89', 'L9G999g999.99' ); ``` Here is the result: ``` to_number ----------- 1234567.89 (1 row) ``` In this example: - `'$1,234,567.89'` is the input string representing a money amount. It includes a dollar sign (`$`), a group separator (`,`), a decimal point (`.`), and numeric digits. - `'L9G999g999.99'` is the format string that the `TO_NUMBER()` function interprets the money amount. Each character in the format string has a specific meaning: - `L`: A local currency symbol (in this case, the dollar sign `$`). - `9`: A digit placeholder. - `G`: The group separator (`,` in this case). - `g`: An optional occurrence of the group separator (`,`), which allows for flexible formatting. - `.`: The decimal point. - `99`: Two-digit placeholders for the fractional part (cents). Since the provided format matches the input string, the function parses the string accordingly and returns the number `1234567.89`. ### 3) Format control If you don't specify .99 in the format string, the `TO_NUMBER()` function will not parse the part after the decimal place. For example: ```sql SELECT TO_NUMBER( '1,234,567.89', '9G999g999' ); ``` It returned `1234567` instead of `1234567.89` as follows: ```text to_number ----------- 1234567 (1 row) ``` ### 4) Format string does not match the input string The following statement uses the `TO_NUMBER()` function to convert a string to a number but the format string does not match: ```sql SELECT TO_NUMBER('1,234,567.89', '999G999.99'); ``` Output: ``` ERROR: numeric field overflow DETAIL: A field with precision 6, scale 0 must round to an absolute value less than 10^6. ``` The `TO_NUMBER()` function issues an error in this case. ## Summary - Use the PostgreSQL `TO_NUMBER()` function to convert a string to a numeric value. --- ## Related docs (String Functions) - [ASCII](https://neon.com/postgresql/postgresql-string-functions/postgresql-ascii) - [CHR](https://neon.com/postgresql/postgresql-string-functions/postgresql-chr) - [CONCAT](https://neon.com/postgresql/postgresql-string-functions/postgresql-concat-function) - [CONCAT_WS](https://neon.com/postgresql/postgresql-string-functions/postgresql-concat_ws) - [FORMAT](https://neon.com/postgresql/postgresql-string-functions/postgresql-format) - [INITCAP](https://neon.com/postgresql/postgresql-string-functions/postgresql-initcap) - [LEFT](https://neon.com/postgresql/postgresql-string-functions/postgresql-left) - [LENGTH](https://neon.com/postgresql/postgresql-string-functions/postgresql-length-function) - [LOWER](https://neon.com/postgresql/postgresql-string-functions/postgresql-lower) - [LPAD](https://neon.com/postgresql/postgresql-string-functions/postgresql-lpad) - [LTRIM](https://neon.com/postgresql/postgresql-string-functions/postgresql-ltrim) - [MD5](https://neon.com/postgresql/postgresql-string-functions/postgresql-md5) - [POSITION](https://neon.com/postgresql/postgresql-string-functions/postgresql-position) - [REGEXP_MATCHES](https://neon.com/postgresql/postgresql-string-functions/postgresql-regexp_matches) - [REGEXP_REPLACE](https://neon.com/postgresql/postgresql-string-functions/regexp_replace) - [REPEAT](https://neon.com/postgresql/postgresql-string-functions/postgresql-repeat) - [REVERSE](https://neon.com/postgresql/postgresql-string-functions/postgresql-reverse) - [REPLACE](https://neon.com/postgresql/postgresql-string-functions/postgresql-replace) - [RIGHT](https://neon.com/postgresql/postgresql-string-functions/postgresql-right) - [RPAD](https://neon.com/postgresql/postgresql-string-functions/postgresql-rpad) - [RTRIM](https://neon.com/postgresql/postgresql-string-functions/postgresql-rtrim) - [SPLIT_PART](https://neon.com/postgresql/postgresql-string-functions/postgresql-split_part) - [SUBSTRING](https://neon.com/postgresql/postgresql-string-functions/postgresql-substring) - [TO_CHAR](https://neon.com/postgresql/postgresql-string-functions/postgresql-to_char) - [TRANSLATE](https://neon.com/postgresql/postgresql-string-functions/postgresql-translate) - [TRIM](https://neon.com/postgresql/postgresql-string-functions/postgresql-trim-function) - [UPPER](https://neon.com/postgresql/postgresql-string-functions/postgresql-upper)