ORACLE: Sorting a String field containing numbers

Question

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;

Hi Andrew, I'm actually out of ideas. I thought of changing the Java code but it will be better if I can display it in the correct way using queries. — user6662778
– user6662778, Commented Mar 23, 2017 at 12:03
Are there any other special page numbers (e.g. 'A', 'page 12', '1+2', '3f', '4ff', '1.5', '1,2,3')? Or only numbers ('1', '2', ...) and number ranges ('1-2', '3-5', ...)? — Thorsten Kettner
– Thorsten Kettner, Commented Mar 23, 2017 at 12:29

Ponder Stibbons · Accepted Answer · 2017-03-23 12:17:48Z

1

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

^{rextester demo}

answered Mar 23, 2017 at 12:17

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

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

1 Comment

user6662778 Over a year ago

Perfect! Thanks a lot! :)

MT0 · Accepted Answer · 2017-03-23 12:15:55Z

0

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;

answered Mar 23, 2017 at 12:15

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

ORACLE: Sorting a String field containing numbers

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related