Problem with Oracle/SQL ORDER BY Statement

Question

i have the following content inside an varchar2 column:

   10.1.2.3
   10.2.3.4
   8.3.4.1
   8.3.2.1
   4.2.1.3
   4.3.2.1
   9.3.1.2

When i query the database i need an result ordered:

4....
8....
9....
10...

the NLS_SORT parameter is set to german, an simple "order by COLUMN DESC/ASC" is not working like excepted. It returns

10.....
8......
9......

any suggestions?

Answer 1

Assuming it's an IP address

SELECT col
  FROM table
 ORDER BY 
(regexp_substr(col, '[^.]+', 1, 1) * 256  * 256  * 256 ) + (regexp_substr(col, '[^.]+', 1, 2) * 256 * 256) + (regexp_substr(col, '[^.]+', 1, 3) * 256 )+ regexp_substr(col, '[^.]+', 1, 4)