I have an alphanumeric data like:
1 1a 1b 2 2b 10 10a
If I sort this data, output will be like:
1 1a 10 10a 2 2b
But I want output as:
1 1a 2 2b 10 10a
How to get this output with Oracle command?
Add a comment
|
2 Answers
So, as I understand, you want to sort by numeric part of your data. For this purpose you can use regular expression (to extract the numeric part) like this:
SQL> select str from
2 (
3 select '1' str from dual union all
4 select '1a' from dual union all
5 select '1b' from dual union all
6 select '2' from dual union all
7 select '2b' from dual union all
8 select '10' from dual union all
9 select '10a' from dual
10 ) t
11 order by to_number(regexp_substr(str, '^[[:digit:]]*')), str
12 /
STR
---
1
1a
1b
2
2b
10
10a
Comments
You can also do the same by separating number and alphanumeric sorting order in order by clause. check below example:
SELECT tt.qdef_grid
FROM qgdm_qdef tt
ORDER BY to_number(substr(tt.qdef_grid,2,2)), substr(tt.qdef_grid,1,1);
