Oracle SQL

Dear Experts,

 In Insert Query I got error message invalid number, How do I cast to zero if it an Invalid number

Jamil MuammarAsked:
Who is Participating?
Mark GeerlingsDatabase AdministratorCommented:
What does your insert statement look like?  Is it like #1 or #2 here?
1.  "insert into [table_name] [optional list of columns] values [list of values]
2. "insert into [table_name] [optional list of columns]  select ... from ...

For either type of "insert..." statement, you can use a function like the one slightwv suggested.

For #1, you will need to apply this function to each value in your "values" list that is going to a NUMBER column in the database.

For #2, the problem is bad data in one of the columns you are selecting, so you will need to use the function on each CHAR or VARCHAR2 column you are selecting that is being inserted into a NUMBER column.
HuaMin ChenSystem AnalystCommented:

If the number is having non-numeric characters inside, you can use a block of codes having Other exception to detect the error.

Otherwise, you can only use


Function to convert it.
slightwv (䄆 Netminder) Commented:
Is this insert statement already in a PL/SQL stored procedure or is it stand-alone from some other code?

I would look at creating your own function to return a zero if you give it invalid input.

Something like this:
create or replace function my_to_number(p_input in varchar2) return number is
junk number;
	junk := to_number(p_input);
	return junk;

	exception when others then return 0;

show errors

select my_to_number('Hello') from dual;
select my_to_number(12345) from dual;

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jamil MuammarAuthor Commented:
It's Oracle Database
HuaMin ChenSystem AnalystCommented:

Use Others exception
Without function -
select case when regexp_instr(yourcolumn,'[^[:digit:]]') > 0 then 0 else to_number(yourcolumn) end as  yourcolumn
from yourtable;
HuaMin ChenSystem AnalystCommented:
Details had been already given to resolve this thread!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.