Oracle - Sorting Alphanumeric values

TABLE NAME: TEMPALPHANUMERIC

NOW TRY FIRING THE FOLLOWING QUERY:

SELECT * FROM TEMPALPHANUMERIC ORDER BY PRODNAME


YOU WIL NOT GET THE DESIRED OUTPUT, AS ONE HAS TO EXTRACT THE DIGITS FROM THE STRING TO ENABLE PROPER SORTING

IF ONE WANTS TO SORT ALPHANUMERIC VALUES, ONE HAS TO EXTRACT THE DIGITS FROM THE STRING AND SORT THERE AFTER. THE BELOW QUERY MAY HELP YOU SORT THE ALPHANUMERIC VALUES.

SELECT ID,PRODNAME,CAST(SUBSTR(PRODNAME,INSTR(PRODNAME,' '),(LENGTH(PRODNAME)- INSTR(PRODNAME,' ')+1)) AS NUMBER) AS EXTRACTDIGIT 
FROM TEMPALPHANUMERIC
ORDER BY EXTRACTDIGIT

OUTPUT: