PalB PalB -4 years ago 121
SQL Question

ORACLE: Sorting a String field containing numbers

I want to display page number in ascending order. But, since the field PAGE is of String datatype, normal 'ORDER BY' considers 10 < 2. I have to make the field PAGE as String because there can be inputs like '3-4'. Can anyone please suggest a way out. I've attached screenshot for reference.

Kindly help.Screenshot

select id
,F_NL
,page
,title
from newsletter_content
where F_NL = '29'
order by page asc;

Answer Source
select page from p 
  order by to_number(nvl(substr(page, 1, instr(page, '-')-1), page))

rextester demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download