TO_NUMBER for alphanumerics 1

[MichaelHuber]

One of my VARCHAR2 fields contains numerics as well as alphanumerics. I'd like to use TO_NUMBER or a similar function against this field, and treat the alphanums as 0.

When I call TO_NUMBER('123'), of course it returns 123.
When I call TO_NUMBER('A123'), I get "ORA-01722: invalid number" ...but I'd like it to treat this as the number 0.

Any suggestions?
Thanks,
Michael

 

[lewisp]

One way is to create a function:

[tt]FUNCTION Char_To_Num (p_str IN VARCHAR2)
RETURN NUMBER IS
l_test NUMBER;
BEGIN
l_test := To_Number(p_str);
Return l_test;
EXCEPTION
WHEN OTHERS
THEN
Return 0;
END Char_To_Num;[/tt]

Then your SQL can look like this

[tt]SELECT Char_To_Num(char_Column)
FROM your_table;[/tt]