Normal Oracle Sort

Question

Inquiry...

select distinct name from myTable 

returns a bunch of values ​​starting with the following sequences of characters ...

ADL*
FG*
FH*
LAS*
TWUP*

Where '*' is the rest of the string.

I want to do this order as follows ...

ADL*
LAS*
TWUP*
FG*
FH*

But then I also want to sort by each name in a standard modulo order. So, an example, if I have the following values

LAS-21A
TWUP-1
FG999
FH3
ADL99999
ADL88888
ADL77777
LAS2

I want it to be sorted like this ...

ADL77777
ADL88888
ADL99999
LAS2
TWUP-1
FG999
FH3

Initially, I thought that I could execute these firmware by following the decoding order (blah) with some similar tricks inside the decoding, but I could not execute it. Any ideas?

Answer 1

Goofy and verbose, but should work:

select name, case when substr (name, 1, 3) = 'ADL'  then 1  
                  when substr (name, 1, 3) = 'LAS'  then 2
                  when substr (name, 1, 4) = 'TWUP' then 3
                  when substr (name, 1, 2) = 'FG'   then 4
                  when substr (name, 1, 2) = 'FH'   then 5
                  else 6
             end SortOrder
from myTable
order by 2, 1;

, 6 - , , . , , , , .

EDIT. , 4 5:

select name, case when substr (name, 1, 3) = 'ADL'  then 1  
                  when substr (name, 1, 3) = 'LAS'  then 2
                  when substr (name, 1, 4) = 'TWUP' then 3
                  when substr (name, 1, 1) = 'F'    then 4
                  else 6
             end SortOrder
from myTable
order by 2, 1;

. , , . , , F *, . , . , substr:

select name, case when substr (name, 1, 1) = 'F'  then 'Z'
                  else name
             end SortOrder
from myTable
order by 2, 1;

Answer 2

, . ( 10g ).

SQL> select cola
  2  from t34
  3  order by decode( regexp_substr(cola, '[[:alpha:]]+')
  4                   , 'ADL' , 10
  5                    , 'LAS',  20
  6                    , 'TWUP',  30
  7                    , 'FG' , 40
  8                    , 'FH' , 50
  9                    , 60 )
 10           , cola
 11  /

COLA
----------
ADL77777
ADL88888
ADL99999
LAS-21A
LAS2
TWUP-1
FG999
FH3

8 rows selected.

SQL>

Oracle OWA_PATTERN.AMATCH() :

SQL> select cola
  2  from t34
  3  order by decode( owa_pattern.amatch(cola, 1, '^[A-Z]+')
  4                   , 'ADL' , 10
  5                    , 'LAS',  20
  6                    , 'TWUP',  30
  7                    , 'FG' , 40
  8                    , 'FH' , 50
  9                    , 60 )
 10           , cola
 11  /

COLA
----------
ADL77777
ADL88888
ADL99999
FG999
FH3
LAS-21A
LAS2
TWUP-1

8 rows selected.

SQL>