CODEWITHSUNDEEP
sqloracle-database
acadia
acadia

Reputation: 2333

Compare number column with String in Oracle

I have couple of columns in Oracle Table. 

EMP_SEQ_NO  Number(5)
Some_COL    Varchar(20)

EMP_SEQ_NO    Some_COL
10             ABCD
11              11
12             Test
13             Tommy
14              14
15              15
16              Ronny

I am trying to do something like 

Select * from EMP where EMP_SEQ_NO=Some_COL

It is throwing Invalid number error.

Please help

Upvotes: 5

Views: 18109

Answers (3)

deejay
deejay

Reputation: 575

to_number will solve this

Select * from EMP where EMP_SEQ_NO= to_number(Some_COL, '99999');

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18767

Both of them are of different types. So, you should cast one of them to the type of the other:

Select * from EMP 
where CAST(EMP_SEQ_NO as varchar(20)) = Some_COL

OR

Select * from EMP 
where EMP_SEQ_NO = CAST(Some_COL as number(5))

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

Using to_char solves your problem.

Select * from EMP where to_char(EMP_SEQ_NO)= to_char(Some_COL)

Upvotes: 5

Related Questions