I need use an order by clause that will sort on alphanumeric values in single Varchar2 column. The sort needs to use the numeric value first, then next numeric value (in case of second or third dot) and then the alpha.
MS Access has a "value" function that makes this fairly easy, but I have been unable to find the same thing in Oracle SQL.
Any ideas would be greatly appreciated.
I have broken it down into a number part and then a text part and then ordered by the two fields, its a little complicated but I can't think of another way
to_number(translate((substr(a,1,instr(a,'.')) || replace(substr(a,instr(a,'.')+1),'.','')),'1qwerty uioplkjhgfdsazxcvbnmQWERTYUIOPLKJHGFDSAZXCVBNM','1 ')) NUM_PART,
ORDER BY 2,3
Thank you for your interest. You are correct in the sort should have the 10.25(a,b) before the 10.8.
I created a table test and inserted the values from my post into it to run your solution. Unfortunately this is the result:
1 SELECT a,
2 to_number(translate((substr(a,1,instr(a,'.')) ||
3 replace(substr(a,instr(a,'.')+1),'. ','')),
4 '1qwertyuioplkjhgfdsazxcvbnmQWERTYUIOPLKJHGFDSAZXC VBNM','1'))
6 translate(a,'a0123456789.','a') ALPHA_PART
7 FROM test
8* ORDER BY 2,3
ORA-01722: invalid number
no rows selected
You did point me the right direction by spliting the numbers into 2 parts, before the first dot and after the first dot. What I ended up doing was stripping out the dots after the first one, stripping out any alpha or spaces, then sorting on the first part (num1) and then the second part (num2), then on column itself to put the alphas in order.
Seems to work so far:
To_number(translate(substr(a,instr(a,'.')+1),'1234 567890. qwertyuioplkjhgfdsazxcvbnmQWERTYUIOPLKJHG
order by 2,3,1