TO_NUMBER replacement

Oracle uses TO_NUMBER to converts a text to a number. An optional format defines the digit conversion rule and may be replaced by a internal mapping to be compatible with PostgreSQL.

Without format

SELECT to_number('1.00') FROM dual;

-- becomes
SELECT to_number('1.00', '9.99');
SELECT to_number('1000') FROM dual;

-- becomes
SELECT to_number('1000', '9999');

With format

⚠ need contribution

Description Oracle NLS PostgreSQL Locale
Returns a comma in the specified position , (comma) , (comma)
Returns a decimal point . (period) . (period)
Returns value with a leading dollar sign. $
Returns trailing/leading zeros. 0 0
Returns value with the specified number of digits 9 9
Returns blanks for the integer part of a fixed-point number B
Returns in the specified position the ISO currency symbol C NLS_ISO_CURRENCY
Returns in the specified position the decimal character D NLS_NUMERIC_CHARACTER D lc_numeric
Returns a value using in scientific notation. EEEE EEEE
Returns in the specified position the group separator G NLS_NUMERIC_CHARACTER lc_numeric
Returns in the specified position the local currency symbol L NLS_CURRENCY G lc_monetary
Returns negative value with a trailing minus sign (-) MI MI
Returns negative value in angle brackets. PR PL ?
Returns a value as Roman numerals in uppercase. RN RN
Returns a value as Roman numerals in lowercase. rn ?
Returns negative/positive value with a trailing/leading minus/plus sign S SG lc_numeric
Returns the smallest number of characters possible. TM
Returns in the specified position the Euro. U NLS_DUAL_CURRENCY
Returns a value multiplied by 10n. V V
Returns the hexadecimal value of the specified number of digits. X

With DEFAULT clause

SELECT to_number('1,00' DEFAULT 0 ON CONVERSION ERROR) FROM dual;

-- becomes
SELECT COALESCE(to_number('1,00', ''), 0);

References:

Edited by Florent Jardin
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information