Sql error "ORA-01722: invalid number"

sql

data-validation

exception-handling

best-practices

byAnton Shumikhin·Nov 9, 2024

The ORA-01722 error appears when an attempt to convert a non-numeric string to a number in SQL goes sideways. This typically happens during an implicit type conversion in the SQL statements. You can dodge this bullet by using the TO_NUMBER() function judiciously:

-- If 'emp_id' has non-numeric strings, this operation got more issues than a magazine:
SELECT * FROM employees WHERE emp_id = 1234;

-- Here comes the superhero, explicit conversion to the rescue!
SELECT * FROM employees WHERE TO_NUMBER(emp_id) = 1234;

Rule of thumb? Validate your data before conversion and handle exceptions like a pro.

Quicksand of data formatting

When your phone number is not just a number

When working with phone numbers or similar data, correct formatting is crucial to prevent our infamous error:

Remove non-numeric characters with regexp_replace before attempting any conversions.

-- Clean up your phone number. Keep it numeric, keep it simple!
INSERT INTO phone_numbers (phone) 
VALUES (regexp_replace('123-456-7890', '[^0-9]+', ''));

After ensuring your inputs are squeaky clean, let the cast function transform your strings to numbers.

Schema ninjas: Check before you leap

Beware of data type mismatches! One wrongly casted variable can rain on your SQL parade. Make sure your SQL operations are in sync with your table column definitions.

Be the Sherlock Holmes of SQL: Review your table schema against your data thoroughly.
Every || operator is a potential suspect. Use Oracle's concatenation operator responsibly to avoid unintentional type conversions.

Trimming down the issues

Spaces: Silent but deadly! Don't let invisible characters like spaces trip you up in your numeric fields:

Trim your data. Use TRIM() or REPLACE() before you set off to convert strings to number types.

One debugger to rule them all

Let's get regular! Using expressions

Pro tip: Use regular expressions to spot and strip non-numeric characters. They're your secret weapon when facing complicated data entries:

REGEXP_LIKE: Your new best friend to filter rows or validate numeric-ness of your data:

-- There are two types of people, those who can extrapolate from incomplete data...
SELECT * FROM table_name WHERE NOT REGEXP_LIKE(column_name, '^[[:digit:]]+$');

Check before you wreck

Check them expressions! Ensure correct type conversion across all expressions. Even a seemingly innocent calculation can pop up the sinister ORA-01722.

Challenge your assumptions

Evolve and adapt. Don't let your assumptions about data tie you down. Don't assume that unnecessary words or special formatting have been exorcised before the data arrives at your DBMS doorstep.