Oracle 11g Release 2

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

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