CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Rohan K
Rohan K

Reputation: 177

Oracle Order By Sorting: Column Values with character First Followed by number

I have column values as

AVG,ABC, AFG, 3/M, 150,RFG,567, 5HJ

Requirement is to sort as below:

ABC,AFG,AVG,RFG,3/M,5HJ,150,567

Any help?

Upvotes: 0

Views: 4087

Answers (5)

borjab
borjab

Reputation: 11655

A) If you only want to change the order of full numberic secuences just create your isNumeric function:

SELECT * FROM table WHERE     isNumeric(field) ORDER BY FIELD
UNION ALL
SELECT * FROM table WHERE NOT isNumeric(field) ORDER BY FIELD

B) If you want to change the order of characters.

Create a funtion that adds a number before every character with a modifier.

  • Number -> 0
  • Other -> 2
  • Letter -> 4

For example:

  • shortHelper("2FT/") => "024F4T2/"
  • shortHelper("AZ") => "4A4Z"
  • shortHelper("Z1") => "4Z01"

Then use "ORDER BY shortHelper(Field)

Upvotes: 0

ryachza
ryachza

Reputation: 4540

This doesn't produce the requested output, but for lexicographic with numbers second TRANSLATE is a simple solution:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions196.htm

select value
from (
  select 'AVG' as value from dual
  union all
  select 'ABC' as value from dual
  union all
  select 'AFG' as value from dual
  union all
  select '3/M' as value from dual
  union all
  select '150' as value from dual
  union all
  select 'RFG' as value from dual
  union all
  select '567' as value from dual
  union all
  select '5HJ' as value from dual
)
order by translate(upper(value), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
;

This shifts all the letters down and numbers to the end.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If you want to sort letters before numbers, then you can test each character. Here is one method:

order by (case when substr(col, 1, 1) between 'A' and 'Z' then 1 else 2 end),
         (case when substr(col, 2, 1) between 'A' and 'Z' then 1 else 2 end),
         (case when substr(col, 3, 1) between 'A' and 'Z' then 1 else 2 end),
         col

Upvotes: 3

Nick
Nick

Reputation: 2514

If there aren't a large number of unique values, build a table that has the value and it's artificial sort order, then order by the sort key. Something like:

create table sort_map
( value     varchar2(35),
  sort_order  number(4)
);

insert into sort_map (value, sort_order) values ('ABC',10);
insert into sort_map (value, sort_order) values ('AFG', 20);
....
insert into sort_map (value, sort_order) values ('150', 70);
insert into sort_map (value, sort_order) values ('567', 80);


--example query
select t.my_col, s.sort_order
from my_table t
join sort_map s
on (t.my_col = s.value)
order by s.sort_order;

Upvotes: 0

Gabor
Gabor

Reputation: 1489

Unfortunately within the sort order numbers are before chars. I could suggest you the put an additional calculated column where you are adding 'ZZZ' in front of the values if they start with a number then you will sort by that virtual column

Upvotes: 0

Related Questions