Solved

Oracle SQL character data need sorted numerically

Posted on 2013-01-31
6
397 Views
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:
1
10
2
20
3
30

How can I get it to do that.????
Thank you.
0
Comment
Question by:newtoperlpgm
  • 4
  • 2
6 Comments
 
LVL 74

Expert Comment

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

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38841373
another variation

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

Author Comment

by:newtoperlpgm
ID: 38841559
is there way to select it as a number rather than having to order by?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

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

Expert Comment

by:sdstuber
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?
0
 
LVL 74

Expert Comment

by:sdstuber
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)
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 12c Default Isolation Level 17 42
oracle forms question 22 42
Toad 12.10 Enterprise visual interface 4 22
why truncate is faster than delete in oracle ? 4 28
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

808 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