order by ip address 1

[TheConeHead]

I have a varchar field that holds ip addresses... how can I get this to order properly. Like say I have these five values:

10.650.555.101
10.650.555.11
10.650.555.105
10.650.555.12
10.650.555.124

simply doing an order by on this field now, it returns:

10.650.555.101
10.650.555.105
10.650.555.11
10.650.555.12
10.650.555.124

and I want it to return:

10.650.555.11
10.650.555.12
10.650.555.101
10.650.555.105
10.650.555.124

How could I get this?

 

[SQLDenis]

create table ip(IP varchar(50))
insert into ip
select '10.650.555.101'
union all
select '10.650.555.11'
union all
select '10.650.555.105'
union all
select '10.650.555.12'
union all
select '10.650.555.124'

select * from ip
order by convert(bigint,replace(ip,'.',''))

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[rafrancisco]

Here's a link that discusses how to sort IP addresses:

http://www.sql-server-helper.com/tips/sort-ip-address.aspx