# Sorting of number in PTO order

Hi ,

I have a table having  only one col. of varchar2 data type .
data are as follows :

1.2
2.0
2.21
8.0
2.2.1
3.0
4.1
3.1
5.1
3.2.4

I want to display is sorted order as :

1.2
2.0
2.2.1
2.21
3.0
3.1
3.2.4
4.1
5.1
8.0

How can I do it
in sql.

!Koushik
###### Who is Participating?

Commented:

I see this is your first question.  Welcome to  EE.

If you need nothing else on this topic then please close the question.
0

Commented:
that's an interesting question because your sample data is sortable  "as is"

select your_column from your_table order by your_column

however, on the assumption that you want to sort your data from left-to-right in numeric order by each dot delimited number then try this...

This could be simplified by using a function for comparing values

I've made the assumption here that you will never have more than 9 numeric pieces to your string if that's not correct then adjust "level < 10" to some number big enough
I've also assumed no one number will be greater than 99 , if that's not correct then adjust "power(100,"  to some number bigger than the largest segment number
`````` SELECT   str
FROM   (SELECT   str,
cnt,
maxcnt,
n,
TO_NUMBER(SUBSTR(str, startpos, endpos - startpos + 1)) * POWER(100, maxcnt - n)
val
FROM   (SELECT   str,
cnt,
MAX(cnt) OVER () maxcnt,
n,
DECODE(n, 1, 1, INSTR(str, '.', 1, n - 1) + 1) startpos,
DECODE(n, cnt, LENGTH(str), INSTR(str, '.', 1, n) - 1) endpos
FROM   (SELECT   str, LENGTH(str) - LENGTH(REPLACE(str, '.', NULL)) + 1 cnt
FROM   yourtable) t,
(    SELECT   LEVEL n
FROM   DUAL
CONNECT BY   LEVEL < 10)
WHERE   n <= cnt))
GROUP BY   str
ORDER BY   SUM(val)
``````
0

Commented:
need anything else?
0

Author Commented:
Sorry for late reply... ya its working fine.....

Thanks!!
Koushik
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.