[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Order by last 2 numbers in varchar colum Oracle

Posted on 2014-02-12
3
Medium Priority
?
407 Views
Last Modified: 2014-02-12
I created a query that returns a list of tools by tool station number.  I need to order the results a few different ways:

If tool_station_num is 3 or 4 characters I need the last 2 characters
If tool_station_num is 2 characters I need the last 1 character

Below is are 2 examples of the the tools that I get from my query and the output I am looking for.

Query outputs tools like this
T134
T141
T201
T237
T333
T340
T342
T351

need to sort like this
T201
T333
T134
T237
T340
T141
T342
T351

Another example from query
T10
T11
T2
T31
T33
T40
T42
T48
T55
T66
T68
T69
T7
T91
T92
T93

desired output
T2
T7
T10
T11
T31
T33
T40
T42
T48
T55
T66
T68
T69
T91
T92
T93
0
Comment
Question by:dustock
[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
  • 2
3 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39854670
select  * from yourtable
order by to_number(regexp_substr( tool_station_num,'[0-9]{1,2}$'))
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39854680
or, if you have a really old version that doesn't support regular expressions try this...

  SELECT *
    FROM yourtable
ORDER BY TO_NUMBER(
             TRANSLATE(
                 SUBSTR(tool_station_num, -2),
                 '0123456789' || SUBSTR(tool_station_num, -2),
                 '0123456789'
             )
         );
0
 
LVL 9

Author Comment

by:dustock
ID: 39854707
This database is Oracle 9i, I was having problems with the first one, but the second one seems to be working fine.  Thanks for the fast response!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

656 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