ORA-01722: invalid number

ORA-01722: invalid number error occurs when attempting to convert a number from a character string that is not a valid numeric literal. In arithmetic functions or expressions, only numeric fields or character fields containing numeric data are allowed. Dates may only be added to or removed from using numeric values. Check the function or expression’s character strings. Retry the operation after double-checking that they only contain integers, a sign, a decimal point, and the letter “E” or “e.”

Oracle uses a valid numeric literal or a character string that contains a valid numeric literal for the numeric data type. If a character string does not contain a valid numeric literal and attempts to assign to a numeric data type variable or participate in any arithmetic operations, an error ORA-01722: invalid number will be thrown. It can only store or perform arithmetic operations if a valid numeric value is assigned to the numeric data type variable. Special characters such as +/- at the beginning or end of the string, a single decimal point, or a ‘E’ or ‘e’ for floating point values in scientific notation are all allowed. To ensure that the numbers in the expression match these specific standards, double-check the character strings in the expression.

The Problem

The error ORA-01722: invalid number might be created by an incorrect character or mistake in your query, bad data in your database tables, or an error in the query logic. The numeric value may have spaces between the numbers or special characters like commas. It’s possible that the character string contains numeric characters that aren’t valid. This will result in an error.

select to_number('123 45') from dual;

Error

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Solution 1

Remove the spaces between numeric digits if the character string contains a valid numeric literal and some gaps in between. This will fix the problem. There may be gaps between digits in cell numbers, zip codes, pin codes, and security numbers. In the numeric number, remove the whitespace characters. This will resolve the error ORA-01722: invalid number.

Error

select to_number('123 45') from dual;

Solution 1

select to_number('12345') from dual;

Solution 2

select cast(regexp_replace('123 45', '[^0-9.]+', '') as number) from dual;

Solution 2

If the character string has a valid numeric literal and some special characters like comma in between, remove the special characters between numeric digits. This will resolve the issue ORA-01722: invalid number. In the quantity and price values, there may be certain unusual characters between the digits. Remove the special characters from the numeric number. This will fix the problem.

Error

select to_number('12,345') from dual;

Solution 1

select to_number('12345') from dual;

Solution 2

select cast(regexp_replace('12,345', '[^0-9.]+', '') as number) from dual;

Solution 3

The error ORA-01722: invalid number will be thrown whenever any arithmetic operation is attempted between a numeric value and a non numeric value. Check the datatype and value of the operands when using the arithmetic operator. Between two numeric operands, the arithmetic operation can be done. A non-numerical value should be converted to a numeric value, or a string operation should be performed.

Error

select 'A10'+123 from dual;

Solution 1

select '10'+123 from dual;

Solution 2

select 'A10'||123 from dual;

Solution 4

The error will be thrown if you use dml commands like insert, update, delete, and select statements when there is a mismatch in data types and values. The data type indicated in the sql query should be used in the value provided to it. If there is a mismatch, an error ORA-01722: invalid number will be thrown.

Error

create table employee ( 
id numeric(2), 
name varchar2(50)
);

insert into employee values('a','test');

Solution 1

insert into employee values(1,'test');

Solution 2

insert into employee values('1','test');

Solution 5

When a numeric value is compared to a non-numerical value or a non-numerical data type, the numeric value cannot be compared. The error ORA-01722: invalid number is going to be thrown. The relational operations with numerical values should be included in the WHERE clause.

Error

create table employee ( 
id numeric(2), 
name varchar2(50)
);

select * from employee WHERE id = 'a';

Solution 1

select * from employee WHERE id = '1';

Solution 2

select * from employee WHERE id = a;

Solution 6

If the numeric value is compared with string column in the sql query, the error will be thrown. The numerical value should be compared with a numeric data type column in the table.

Error

create table employee ( 
id numeric(2), 
name varchar2(50),
age numeric(2)
);

select * from employee where name between 1 and 100;

Solution 1

select * from employee where age between 1 and 100;

Solution 2

select * from employee where age between '1' and '100';

Solution 7

The error ORA-01722: invalid number will be thrown if you add a date with a character string. A character string cannot contain the date. The date can be used with a numeric number to increase the given date by that amount.

Error

select 'Today is ' + sysdate from dual;

Solution 1

select 'Today is ' || sysdate from dual;

Solution 2

select 1 + sysdate from dual;