Skip to Main Content
  • Questions
  • Convert the varchar vaules into number

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 30, 2017 - 2:33 am UTC

Last updated: March 30, 2017 - 4:19 am UTC

Version: oracel 11g

Viewed 1000+ times

You Asked

Hi Tom,

I am created the column "seqnum" as varchar2 . I need to show the values are order by ascending

while selecting the values

100_1
10004_3
1002_21
123_2
10_45
2_3
10004_1
10004_5

I need to show the results as

2_3
10_45
100_1
123_2
1002_21
10004_1
10004_3
10004_5


I am using the below query

select * from ORDER_BY cast(seqnum as int) ASC.

Can u help me out ?

and Connor said...

I assume the two sides of the "_" are number ?

Then you could do:

order by
to_number(substr(seqnum,1,instr(seqnum,'_')-1)),
to_number(substr(seqnum,instr(seqnum,'_')+1))



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