SQL error ORA-01722 invalid number

Question

INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598');
INSERT INTO CUSTOMER VALUES (3,'LUU','Barry','5  Jones St Malvern','0413 591 341');
INSERT INTO CUSTOMER VALUES (4,'JONES','Michael','7  Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (5,'MALADY','Betty','27 Smith St Knox','0418 418 347');

This query is giving me error
Can you check and tell me the error?

Answer 1

When an effort is made to convert a character string into a number and the string cannot be turned into a number, an ORA-01722 error occurs.

Without looking at your table design, it appears like you're attempting to convert the numeric sequence at the end of your values list to a number, and the spaces delimiting it are causing this problem. However, depending on the information you've provided, it might occur on any field (other than the first one).

One way to solve it can be by removing non-numeric characters then cast it as a number.

cast(regexp_replace('0419 853 694', '[^0-9]+', '') as number)