Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Kiran.

Asked: July 07, 2016 - 9:12 am UTC

Answered by: Chris Saxon - Last updated: July 07, 2016 - 2:45 pm UTC

Category: SQL*Plus - Version: oracle

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: On Sorts, Selecting, and Selectivity

You Asked

Hi all,
I have an issue with column datatype.Can you please look at this issue.
I have two tables emp1 and emp2 with empno column in both tables
in table emp1 column empno is nvarchar2 and in table emp2 column empno is number datatype
I am trying to insert data from emp1 to emp2 using below query and its throwing invalid number error
`insert into emp2 (empno,deptname,ename) select empno,deptname,ename from emp1;`
i tried adding to_number to above code but still no luck
`insert into emp2 (empno,deptname,ename) select to_number(empno),deptname,ename from emp1;`
can anyone please review and let me know what am i missing.

My table scripts for both tables are:

CREATE TABLE XXTEST.EMP1
(
EMPNO NVARCHAR2(4) NOT NULL,
ENAME NVARCHAR2(10 BYTE),
DEPTNAME NVARCHAR2(10 BYTE)
)

CREATE TABLE XXTEST.EMP2
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
DEPTNAME VARCHAR2(10 BYTE)
)

and we said...

Presumably because there are values in emp1.empno that aren't numbers!

If there are non-numbers, you could find them with something like:

create table T ( x nvarchar2(2));

insert into t values ('a1');
insert into t values ('b1');
select * from t
where  not regexp_like (x, '^[0-9]*$');

X
--
a1
b1

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Know your data.

July 07, 2016 - 2:24 pm UTC

Reviewer: Paul from Ottawa, ON, CA

That regexp will catch employee ids with anything other than a numeric but it will gloss over employee ids like '0101'. might want to add a check for leading zero.

drop table t;
create table T ( x nvarchar2(2));
insert into t values ('02');
insert into t values ('1A');
insert into t values ('a1');
insert into t values ('b1');
insert into t values ('11');
select * from t
where  not regexp_like (x, '^[0-9]*$') or regexp_like (x,'^0.*')

Table dropped.
Table created.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.

X
--
02
1A
a1
b1

4 rows selected.


Chris Saxon

Followup  

July 07, 2016 - 2:30 pm UTC

True. Though if you're loading numbers from a string with leading zeros, they'll go into a number just fine:

drop table t;
drop table t2 purge;
create table T ( x nvarchar2(2));
insert into t values ('02');
insert into t values ('1A');

create table t2 (
  x int
);

insert into t2
  select x
  from   t
  where  regexp_like (x, '^[0-9]*$');

select * from t2;

         X
----------
         2


Presumably if the target data type is a number, leading zeros don't matter...

Very True

July 07, 2016 - 2:40 pm UTC

Reviewer: Paul from Ottawa, ON, CA

about leading zeroes and to_number(). I should have tested that :(
I think that it is important know the data BEFORE attempting to load it. or at least using Test Driven Development where you try and break it before deploying it.

Chris Saxon

Followup  

July 07, 2016 - 2:45 pm UTC

"I think that it is important know the data BEFORE attempting to load it"

Yes! And have a plan for what to do with rows that won't load for some reason...

July 07, 2016 - 2:41 pm UTC

Reviewer: Kiran Deekonda


July 08, 2016 - 10:20 am UTC

Reviewer: Kiran Deekonda