Oracle SQL character data need sorted numerically

Posted on 2013-01-31
Medium Priority
Last Modified: 2013-01-31
I have a column in my database that has data that begins with numbers but can have letters, that is why it is a varchar field.  For example, 1, 1a, 2, 2a, 3, 3a, 4,5,6, 7, 8, 9 10
I need to sort it in ascending order, but it sorts like the following:

How can I get it to do that.????
Thank you.
Question by:newtoperlpgm
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 74

Expert Comment

ID: 38841367
select * from yourtable order by to_number(regexp_replace(yourcharacterfield,'[^0-9]'));
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 38841373
another variation

select * from yourtable order by to_number(regexp_substr(yourcharacterfield,'^[0-9]+'));

Author Comment

ID: 38841559
is there way to select it as a number rather than having to order by?
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!


Author Comment

ID: 38841635
I tried the following but it doesn't work.
select regexp_subst(SAMPLE_NO,'^[0-9]+') from Table
LVL 74

Expert Comment

ID: 38841640
put either of the conditions I used in the order by

select to_number(regexp_substr(yourcharacterfield,'^[0-9]+')) from yourtable

will return '123abc'  as 123

when you say "doesn't work"  - what doesn't work about it?

wrong results, error, no results, keyboard catches fire?
LVL 74

Expert Comment

ID: 38841651
simple test cases of either method

select to_number(regexp_substr(sample_no,'^[0-9]+')) from
(select '123abc' sample_no from dual);

select to_number(regexp_replace(sample_no,'[^0-9]')) from
(select '123abc' sample_no from dual)

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question