# "Safe" TO_NUMBER()

OracleType ConversionOra 01722## Oracle Problem Overview

```
SELECT TO_NUMBER('*') FROM DUAL
```

This obviously gives me an exception: > ORA-01722: invalid number

Is there a way to "skip" it and get `0`

or `NULL`

instead?

The whole issue: I have `NVARCHAR2`

field, which contains numbers and not almost ;-) (like `*`

) and I need to select the biggest number from the column.

Yes, I know it is a terrible design, but this is what I need now... :-S

**UPD**:

For myself I've solved this issue with

```
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)
```

## Oracle Solutions

## Solution 1 - Oracle

From `Oracle Database 12c Release 2`

you could use **TO_NUMBER** with `DEFAULT ... ON CONVERSION ERROR`

:

```
SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
```

Or `CAST`

:

```
SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
```

## Solution 2 - Oracle

```
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0)
```

will also get numbers with scale > 0 (digits to the right of the decimal point).

## Solution 3 - Oracle

I couldn't find anything better than this:

```
function safe_to_number(p varchar2) return number is
v number;
begin
v := to_number(p);
return v;
exception when others then return 0;
end;
```

## Solution 4 - Oracle

```
select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;
```

It will convert *123* to **123**, but *123a* or *12a3* to **0**.

## Solution 5 - Oracle

Fitting the original question and rather old skool

```
select a, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0) from
(
select '1' a, 'not a number' b from dual
union
select '2' a, '1234' b from dual
)
```

## Solution 6 - Oracle

It's probably a bit messy rolling your own regexp to test for a number, but the code below might work. I think the other solution by Gabe involving a user defined function is more robust since you are using the built in Oracle functionality (and my regexp is probably not 100% correct) but it might be worth a go:

```
with my_sample_data as (
select '12345' as mynum from dual union all
select '54-3' as mynum from dual union all
select '123.4567' as mynum from dual union all
select '.34567' as mynum from dual union all
select '-0.3462' as mynum from dual union all
select '0.34.62' as mynum from dual union all
select '1243.64' as mynum from dual
)
select
mynum,
case when regexp_like(mynum, '^-?\d+(\.\d+)?$')
then to_number(mynum) end as is_num
from my_sample_data
```

This will then give the following output:

```
MYNUM IS_NUM
-------- ----------
12345 12345
54-3
123.4567 123.4567
.34567
-0.3462 -0.3462
0.34.62
1243.64 1243.64
```

## Solution 7 - Oracle

```
select DECODE(trim(TRANSLATE(replace(replace(A, ' '), ',', '.'), '0123456789.-', ' ')),
null,
DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '.', INSTR(replace(replace(A, ' '), ',', '.'), '.') + 1),
0,
DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '-', 2),
0,
TO_NUMBER(replace(replace(A, ' '), ',', '.'))))) A
from (select '-1.1' A from DUAL union all select '-1-1' A from DUAL union all select ',1' A from DUAL union all select '1..1' A from DUAL) A;
```

This code excludes such strings as: -1-1, 1..1, 12-2 and so on. And I haven't used regular expressions here.

## Solution 8 - Oracle

A combination of previous solutions (from @sOliver and @Mike Meyers) and trying to grab as much numbers as possible by removing the last '$' from REGEXP.

It can be used to filter the actual number from a configuration table, and have a "kind-of" comment next to the number as '12 Days'.

```
with my_sample_data as (
select '12345' as mynum from dual union all
select '123.4567' as mynum from dual union all
select '-0.3462' as mynum from dual union all
select '.34567' as mynum from dual union all
select '-.1234' as mynum from dual union all
select '**' as mynum from dual union all
select '0.34.62' as mynum from dual union all
select '24Days' as mynum from dual union all
select '42ab' as mynum from dual union all
select '54-3' as mynum from dual
)
SELECT mynum,
COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num
FROM my_sample_data;
```

would give

```
MYNUM IS_NUM
12345 12345
123.4567 123.4567
-0.3462 -0.3462
.34567 0.34567
-.1234 -0.1234
** 0
0.34.62 0.34
24Days 24
42ab 42
54-3 54
```

`12345 12345`

123.4567 123.4567

-0.3462 -0.3462

.34567 0.34567

-.1234 -0.1234

** 0

0.34.62 0.34

24Days 24

42ab 42

54-3 54

## Solution 9 - Oracle

Best method seems to be the function solution but if you don't have necessary privileges in the environment you are struggling (like me), then you can try this one:

```
SELECT
CASE
WHEN
INSTR(TRANSLATE('123O0',
' qwertyuıopğüasdfghjklşizxcvbnmöçQWERTYUIOPĞÜASDFGHJKLŞİZXCVBNMÖÇ~*\/(){}&%^#$<>;@€|:_=',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
),
'X') > 0
THEN 'Y'
ELSE 'N'
END is_nonnumeric
FROM DUAL
```

By the way: In my case the problem was due to "," and "." :) So take that into consider. Inspired from this one. Also this one seems more concise.

By the way 2: Dear Oracle, can you please create some built-in functions for such small but invaluable needs?