CODEWITHSUNDEEP
sqloracle-database
user6662778
user6662778

Reputation:

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;

Upvotes: 0

Views: 45

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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

rextester demo

Upvotes: 1

MT0
MT0

Reputation: 167832

You can check for the presence of a - character and extract the preceding number:

ORDER BY CASE
           WHEN INSTR( page, '-' ) > 0
           THEN TO_NUMBER( SUBSTR( page, 1, INSTR( page, '-' ) - 1 ) )
           ELSE TO_NUMBER( page )
         END;

Upvotes: 0

Related Questions