Oracle SQL character data need sorted numerically

Posted on 2013-01-31
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
  • 4
  • 2
LVL 73

Expert Comment

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

Accepted Solution

sdstuber earned 500 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?
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

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

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 73

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question About Creating Primary Key Constraints on Oracle Tables... 5 38
SQL Query 34 80
Access 2010 Query Syntax 5 18
Update data using formula 22 19
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.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

914 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now