ORA-01722: invalid number - Avoiding characters from numeric values in same column

Issue :


I have found column CG_AMOUNT has non numeric values for bellow query.

SELECT COUNT(*) AS CHARGE_CUSTOMER_COUNT FROM CM_CHRG_CX;

Oracle gives bellow error code : ORA-01722: invalid number


Solution : use REGEXP_LIKE(CG_AMOUNT, '^[[:digit:]]+$');

SELECT COUNT(*) AS CHARGE_CUSTOMER_COUNT FROM CM_CHRG_CX WHERE REGEXP_LIKE(CG_AMOUNT, '^[[:digit:]]+$');