Solved

Oracle SQL character data need sorted numerically

Posted on 2013-01-31
6
398 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
[X]
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
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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

734 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