Solved

Oracle SQL character data need sorted numerically

Posted on 2013-01-31
6
396 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 73

Expert Comment

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

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
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.

 

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 73

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 73

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

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

773 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