ORA-01722: invalid number while selecting data from a table

 Problem Statement

SELECT * FROM UTILIZATION_REPORT_DATA WHERE AMOUNT_PAID_ONT>0 

ORA-01722: invalid number

Solution

identify rows where AMOUNT_PAID_ONT contains non-numeric characters, which usually cause ORA-06502 / invalid number errors.

However, the condition needs a small correction.

---

✅ Correct Query to Identify Problem Rows

SELECT *
FROM utilization_report_data
WHERE RTRIM(AMOUNT_PAID_ONT, '0123456789') IS NOT NULL;

⚠ Why this sometimes still misses rows

• RTRIM() removes digits only from the right side

• If alphabets/symbols appear in the middle or left, they won’t be detected

---

✅ Recommended & SAFEST METHOD (BEST PRACTICE)

Use REGEXP_LIKE to catch any non-numeric value:

SELECT *
FROM utilization_report_data
WHERE NOT REGEXP_LIKE(AMOUNT_PAID_ONT, '^[0-9]+$');

✔ This identifies:

1. 100A
2. A100
3. 100.50
4. 100,000
5. spaces
6. NULL-handling separately if needed

---

✅ If decimal values are allowed (e.g. 100.50)

SELECT *
FROM utilization_report_data
WHERE NOT REGEXP_LIKE(AMOUNT_PAID_ONT, '^[0-9]+(\.[0-9]+)?$');

---

✅ If column is VARCHAR but used in NUMBER conversion

To safely test conversion:

SELECT *
FROM utilization_report_data
WHERE TO_NUMBER(AMOUNT_PAID_ONT DEFAULT NULL ON CONVERSION ERROR) IS NULL
  AND AMOUNT_PAID_ONT IS NOT NULL;

(Oracle 12c+)

---

✅ Quick Count of Bad Rows

SELECT COUNT(*)
FROM utilization_report_data
WHERE NOT REGEXP_LIKE(AMOUNT_PAID_ONT, '^[0-9]+$');

---

📌 Recommendation

If this column represents money:

• Store it as NUMBER

• Validate before insert/update

• Or clean data once and migrate