CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Killer
Killer

Reputation: 188

ORACLE Order By number first in string field

I can not figure how to set this order by in this query. I want to order by first by numbers and then by char. Maybe changing each character to HEX would work? I dont know. I will put the result when I order by 

COLA   COLB
----   ----
00     1
000A   2
000B   2
000C   2
000D   2
000F   2
000G   2
000H   2
000I   2
0000   2
0001   2
0002   2
0003   2
00030A 3
00030B 3
000300 3

And what I want to get as result is this:

COLA   COLB
----   ----
00     1
0000   2
0001   2
0002   2
0003   2
000A   2
000B   2
000C   2
000D   2
000F   2
000G   2
000H   2
000I   2
000300 3
00030A 3
00030B 3

Thats my query:

with table1
as (
  select '00' COLA, 1 COLB from dual union
  select '000A' COLA, 2 COLB from dual union
  select '000B' COLA, 2 COLB from dual union
  select '000C' COLA, 2 COLB from dual union
  select '000D' COLA, 2 COLB from dual union
  select '000E' COLA, 2 COLB from dual union
  select '000F' COLA, 2 COLB from dual union
  select '000G' COLA, 2 COLB from dual union
  select '000H' COLA, 2 COLB from dual union
  select '000I' COLA, 2 COLB from dual union
  select '0000' COLA, 2 COLB from dual union
  select '0001' COLA, 2 COLB from dual union
  select '0002' COLA, 2 COLB from dual union
  select '0003' COLA, 2 COLB from dual union
  select '00030A' COLA, 3 COLB from dual union
  select '00030B' COLA, 3 COLB from dual union
  select '000300' COLA, 3 COLB from dual 
)
select * from table1 order by COLB, COLA

I already tried to use LPAD but it doesn't worked.

Upvotes: 0

Views: 4825

Answers (4)

Kevin Burton
Kevin Burton

Reputation: 11936

You could use the TRANSLATE function :

  with table1 as (
  select '00' COLA, 1 COLB from dual union
  select '000A' COLA, 2 COLB from dual union
  select '000B' COLA, 2 COLB from dual union
  select '000C' COLA, 2 COLB from dual union
  select '000D' COLA, 2 COLB from dual union
  select '000E' COLA, 2 COLB from dual union
  select '000F' COLA, 2 COLB from dual union
  select '000G' COLA, 2 COLB from dual union
  select '000H' COLA, 2 COLB from dual union
  select '000I' COLA, 2 COLB from dual union
  select '0000' COLA, 2 COLB from dual union
  select '0001' COLA, 2 COLB from dual union
  select '0002' COLA, 2 COLB from dual union
  select '0003' COLA, 2 COLB from dual union
  select '00030A' COLA, 3 COLB from dual union
  select '00030B' COLA, 3 COLB from dual union
  select '000300' COLA, 3 COLB from dual 
)
select * from table1 order by COLB, TRANSLATE(COLA,'0123456789','0123456789'),TRANSLATE(COLA,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ')

Upvotes: 1

BriteSponge
BriteSponge

Reputation: 1054

When I run your example in my database I get the 'correct' sort order as per your list. Check you NLS_SORT setting; mine is set to BINARY.

Try it out by changing it for the session;

ALTER SESSION SET nls_sort='BINARY';

There are lots of possibilities for this parameter so if you want to experiment you can find the possibilities in the V$NLS_VALID_VALUES view.

I've also just spotted that you can do the following;

SELECT * FROM table1 ORDER BY colb, NLSSORT(cola, 'NLS_SORT=BINARY')

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269543

One method is to extract the number:

order by colb,
         cast(regexp_substr(cola, '[0-9]', 1, 1) as number),
         cola

Upvotes: 2

S.Ali
S.Ali

Reputation: 29

please try this

with table1
as (
 select '00' COLA, 1 COLB from dual union
select '000A' COLA, 2 COLB from dual union
select '000B' COLA, 2 COLB from dual union
select '000C' COLA, 2 COLB from dual union
select '000D' COLA, 2 COLB from dual union
select '000E' COLA, 2 COLB from dual union
select '000F' COLA, 2 COLB from dual union
select '000G' COLA, 2 COLB from dual union
select '000H' COLA, 2 COLB from dual union
select '000I' COLA, 2 COLB from dual union
select '0000' COLA, 2 COLB from dual union
select '0001' COLA, 2 COLB from dual union
select '0002' COLA, 2 COLB from dual union
select '0003' COLA, 2 COLB from dual union
select '00030A' COLA, 3 COLB from dual union
select '00030B' COLA, 3 COLB from dual union
select '000300' COLA, 3 COLB from dual 
)
select * from table1 order by COLB, to_number(regexp_substr(COLA, '^[[:digit:]]*')), COLA

Upvotes: 1

Related Questions