There are many situations where you need to pre-process the received data in order to store it in the data warehouse. For instance, you many receive currency data with currency symbol. You have pre-process currency column to remove the dollar ($) symbol and store clean value in the database. Amazon Redshift support TO_NUMBER function, you can use it to remove unwanted characters from numeric value.
In this article, we will check how to use TO_NUMBER type conversion function with its different format. We will also check different examples to use different format parameters in TO_NUMBER function.
Amazon Redshift TO_NUMBER Function
Amazon Redshift TO_NUMBER function converts a character string containing numeric values to a numeric.
Following is the syntax of Redshift TO_NUMBER function.
to_number(string, format);Where, string is a string to be converted. And argument format is a format string that indicates how the character string should be parsed to create the numeric value.
Amazon Redshift TO_NUMBER Argument Details
The format that will be used to convert string to a number. It can be one of the following and you can use many combinations of these argument in your TO_NUMBER function.
| Parameter | Explanation |
|---|---|
| 9 | Value without leading zeros |
| 0 | Value with leading zeros |
| . | Decimal |
| , | Group separator |
| PR | Negative value in angle brackets |
| S | Sign |
| L | Currency symbol |
| D | Decimal |
| G | Group separator |
| MI | Minus sign (for negative numbers) |
| PL | Plus sign (for positive numbers) |
| SG | Plus/minus sign (for positive and negative numbers) |
| RN | Roman numerals |
| TH | Ordinal number suffix |
| th | Ordinal number suffix |
| V | Shift digits |
| EEEE | Scientific notation |
Amazon Redshift TO_NUMBER Examples
Following are the some of examples with different augments.
Redshift TO_NUMBER to remove Currency Sign
Following example converts string containing the currency symbol (dollar $) to numeric.
dev=# select to_number('$12,454.8', 'L99G999D9');
to_number
-----------
12454.8
(1 row)Following example handles the negative currency value
dev=# select to_number('$-12,454.8', 'LMI99G999D9');
to_number
-----------
-12454.8
(1 row)Note that, we have used ‘D’ and ‘G’ to specify the decimal and group separator. You can also use comma (,) and period (.) to specify same.
Redshift Convert String to Numeric
Following example convert string containing a numeric value to a numeric or decimal value.
dev=# select to_number('12,454.8', '99,999.9');
to_number
-----------
12454.8
(1 row)Related Articles,
Hope this helps 🙂