How to Convert a String to a Number in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Use TO_NUMBER with a format model for currency or separators. On 12.2+, DEFAULT ... ON CONVERSION ERROR and VALIDATE_CONVERSION handle dirty data.
SELECT TO_NUMBER('1234.56') AS n FROM dual;
SELECT TO_NUMBER('$1,234.56', '$999,999.99') AS n FROM dual;Handling bad input (12.2+)
DEFAULT ... ON CONVERSION ERROR substitutes a fallback value instead of raising an error when the string cannot be parsed, which is useful when converting an entire column of loosely typed text.
SELECT TO_NUMBER('abc' DEFAULT 0 ON CONVERSION ERROR) AS n FROM dual;Finding the bad rows first
VALIDATE_CONVERSION checks whether a value can be converted without actually converting it, returning 1 for valid and 0 for invalid, which makes it useful for filtering a staging table before a real conversion.
SELECT order_id, raw_amount
FROM staging_orders
WHERE VALIDATE_CONVERSION(raw_amount AS NUMBER) = 0;Oracle also converts strings to numbers implicitly in comparisons and arithmetic, which works fine right up until one row has a non-numeric value and the whole statement fails with ORA-01722: invalid number. That failure can surface on a query that ran cleanly for months, the moment a single bad row lands in the table; convert explicitly with TO_NUMBER instead of relying on implicit conversion.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free