Oracle SQL Order by solution

[Jeremy Wilson]

I have the following column values that I need to have sorted but not by the default sort order. If anyone has any ideas that would be greatly appreciated.

Column values:

TEST_1
TEST_2
TEST_3
TEST_4
TEST_11
TEST_5

By default order by does this

TEST_1
TEST_11
TEST_2
TEST_3
TEST_4
TEST_5

I need this

TEST_1
TEST_2
TEST_3
TEST_4
TEST_5
TEST_11

[Mark Spritzler]

Well, you can substring starting just past the "_" then convert the String to an int and then you will get what you are looking for

so something like, but I don't remember the exact syntax

to_number(substr(field,CHAR_AT(field,'_')+1))

So it find where the "_" is then gets the rest of the string past that number, so then you are left with just the number part. Then you convert that to a number and that equation would be in your order by.

Mark