help with sort

I have a column which has values as
1
2
2.1
3
4
2.1.1
2.1.1.1
2.1.2
2.1.3
2.2
2.1.4
2.3
2.99
2.99.1
2.1.5
2.99.1.1
2.1.6
2.100
2.100.1
2.101
2.102
2.1.7
and i need to get this sorted as
1
2
2.1
2.1.1
2.1.1.1
2.1.2
2.1.3
2.1.4
2.1.5
2.1.6
2.1.7
2.2
2.99
2.99.1
2.99.1.1
2.100
2.100.1
2.101
2.102
Is this comething doable thru sql. any help will be greatly appreciated.
Thanks
Message posted via http://www.webservertalk.com>> Is this comething doable thru sql.
Are these IP addresses or something? Is it always less than or equal to 4
digits in the string? If such sorting is a business requirement, you might
want to consider a better schema. You post somewhat exemplifies the
complexity behind queries involving tables with multiple values crammed in a
single column.
One quick & dirty way is to do:
SELECT col
FROM tbl
ORDER BY CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 4 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 3 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 2 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 1 ) AS INT );
You can find details about PARSENAME and REPLICATE functions in SQL Server
Books Online. The idea is a extract each component ( as identified by a
dot ) of the string and then sort them accordingly. The type conversion to
INT is to avoid character based sorting which is not what is specified in
your sample results.
Anith