SQL error ORA-01722 invalid number

0 votes
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?

Feb 15 in Database by Vaani
• 7,020 points
397 views

1 answer to this question.

0 votes

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)
answered Feb 15 by Neha
• 8,560 points

Related Questions In Database

0 votes
1 answer
0 votes
1 answer

Error:Import SQL dump into PostgreSQL database

Hello, Here is the command you are looking ...READ MORE

answered May 4, 2020 in Database by Niroj
• 82,720 points
2,123 views
0 votes
0 answers

ORA-00904: invalid identifier

I am getting this error while doing ...READ MORE

May 20 in Database by Kichu
• 19,040 points
51 views
0 votes
2 answers

SQL vs NoSQL

Well, it depends on what you are ...READ MORE

answered Aug 11, 2018 in Database by slayer
• 29,310 points
347 views
0 votes
1 answer

Please name some online websites to compile and run PL/SQL?

For executing Oracle SQL queries and PL/SQL ...READ MORE

answered Feb 11 in Database by Neha
• 8,560 points
72 views
0 votes
1 answer

Number of rows affected by an UPDATE in PL/SQL

You can try using the following query: SELECT ...READ MORE

answered Feb 14 in Database by Vaani
• 7,020 points
92 views
0 votes
1 answer

Automating Oracle script with nolio

Depending upon the details of your script ...READ MORE

answered Jul 17, 2018 in Other DevOps Questions by ajs3033
• 7,280 points
324 views
0 votes
1 answer

Is there any boolean type in Oracle database?

Nope. I don't think there is one But ...READ MORE

answered Oct 10, 2018 in Database by Neha
• 6,300 points
289 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

answered Feb 4 in Database by Neha
• 8,560 points
357 views
0 votes
1 answer

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the ...READ MORE

answered Feb 4 in Database by Neha
• 8,560 points
116 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP