Compare number column with String in Oracle

Question

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

hkutluay · Accepted Answer · 2015-08-17 13:29:22Z

5

Using to_char solves your problem.

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

answered Aug 17, 2015 at 13:29

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raging Bull · Accepted Answer · 2015-08-17 13:29:56Z

0

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))

answered Aug 17, 2015 at 13:29

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Comments

deejay · Accepted Answer · 2015-08-17 13:49:16Z

0

to_number will solve this

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

answered Aug 17, 2015 at 13:49

deejay

5751 gold badge8 silver badges24 bronze badges

1 Comment

Boneist Over a year ago

Really? select to_number('ABCD', '99999') from dual; gives me ORA-01722: invalid number

Collectives™ on Stack Overflow

Compare number column with String in Oracle

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related