By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (value) AS
SELECT '0' FROM DUAL UNION ALL
SELECT '+00001234' FROM DUAL UNION ALL
SELECT '-00001234' FROM DUAL UNION ALL
SELECT '-00001234.' FROM DUAL UNION ALL
SELECT '-00001234.5678' FROM DUAL UNION ALL
SELECT '.1234' FROM DUAL UNION ALL
SELECT '.1234e4' FROM DUAL UNION ALL
SELECT '.1234E-004' FROM DUAL UNION ALL
SELECT '1234E-004' FROM DUAL
9 rows affected
SELECT value, CAST(value AS NUMBER) FROM table_name;
| VALUE | CAST(VALUEASNUMBER) |
|---|---|
| 0 | 0 |
| +00001234 | 1234 |
| -00001234 | -1234 |
| -00001234. | -1234 |
| -00001234.5678 | -1234.5678 |
| .1234 | .1234 |
| .1234e4 | 1234 |
| .1234E-004 | .00001234 |
| 1234E-004 | .1234 |
SELECT value FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
| VALUE |
|---|
| ., |
ALTER SESSION SET NLS_TERRITORY = 'France';
SELECT value FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
| VALUE |
|---|
| , |
SELECT value, CAST(value AS NUMBER) FROM table_name;
ORA-01722: invalid number
DELETE FROM table_name WHERE value LIKE '%.%';
5 rows affected
SELECT value, CAST(value AS NUMBER) FROM table_name;
| VALUE | CAST(VALUEASNUMBER) |
|---|---|
| 0 | 0 |
| +00001234 | 1234 |
| -00001234 | -1234 |
| 1234E-004 | ,1234 |