?
Solved

Not correct IP sorting in DBGRID

Posted on 2007-07-20
8
Medium Priority
?
235 Views
Last Modified: 2010-04-05
Is it possible to show IP adresses in DBGRID in correct way ???
if i order by ip adress i get not correct sorting :/
*.*.6.10
*.*.6.101
*.*.6.11
*.*.6.12
*.*.6.13
0
Comment
Question by:selas
  • 5
  • 2
8 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 19531702
yup, but you have to sort them as integers

ip = a.b.c.d

int_ip = a*256^3 + b*256^2 + c*256^1 + d*256^0

ziolko.
0
 
LVL 4

Expert Comment

by:spk2000ar
ID: 19533184
Another way is formating IP number as string of XXX.XXX.XXX.XXX... that is... each block has to have 3 digits... completed with leading zeros if you must.

speaker.
0
 

Author Comment

by:selas
ID: 19557891
How to do this with SQL ???
select * from table order by IP
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Expert Comment

by:spk2000ar
ID: 19558360
IP field is an string field?
With which database are you working with

Looking for a solution under LocalSQL with BDE...

But, perhaps the best solution is to store the fields as a string just formated as i stated above (xxx.xxx.xxx.xxx). Then if you want to show ip numbers like 192.168.1.1 instead of 192.168.001.001 you can include a calculated field and reformat the string to its original value with Delphi function.
0
 
LVL 4

Accepted Solution

by:
spk2000ar earned 1500 total points
ID: 19558518
You can also produce the calculated fied in SQL:

select trim(both ' ' from (cast(cast(substring(ipf from  1 for 3) as integer) as char(3)))) + '.' +
           trim(both ' ' from (cast(cast(substring(ipf from  5 for 3) as integer) as char(3)))) + '.' +
         trim(both ' ' from (cast(cast(substring(ipf from  9 for 3) as integer) as char(3)))) + '.' +
         trim(both ' ' from (cast(cast(substring(ipf from 13 for 3) as integer) as char(3)))) as ipnum, ip
from ipnumbers.db
order by ip

This suppose you have a table named IPNUMBERS.DB with a field named IP as Char(15).
Existing data:
192.168.001.010
192.168.001.101
192.168.001.011
192.168.001.001

The result should be (tested with Database Desktop with Paradox Table):

192.168.1.1
192.168.1.10
192.168.1.11
192.168.1.101
0
 

Author Comment

by:selas
ID: 19558756
yes IP is a string field
i use microsoft SQL server
and IP adresses stored as 192.168.1.15
0
 
LVL 4

Expert Comment

by:spk2000ar
ID: 19558865
I do not know if you can in MS-SQL, but you may parse each part of IP string as an integer, then order as

select parsepart1asinteger as part1,
parsepart2asinteger as part2,
parsepart3asinteger as part3,
parsepart4asinteger as part4,
ip
from ipnumbers
order part1, part2, part3, part4

where parsepartNasinteger is a sql function like:

cast( substring(ip from N1 for N2) as integer)

where N1 and N2 has to be the value of the location of the separator point (except for N1 in first string and N2 in last string).

I really do not know if there is a MS-SQL function that do this work. Perhaps you have to ask this question in MS-SQL Server zone.

Hope this help!!!
0
 
LVL 4

Expert Comment

by:spk2000ar
ID: 19560721
MsSQL sintax... probably :-)... can test it

select cast(substring(ip from 1 for charindex('.',ip)-1) as integer) as part1,
          cast(substring(ip from charindex('.',ip)+1 for charindex('.',ip, charindex('.',ip)+1) as integer) as part2,
          cast(substring(ip from charindex('.',ip, charindex('.',ip)+1)+1 for charindex('.',ip, charindex('.',ip, charindex('.',ip)+1)+1) as integer) as part3,
          cast(substring(ip from charindex('.',ip, charindex('.',ip, charindex('.',ip)+1)+1) +1 for charindex('.',ip, charindex('.',ip, charindex('.',ip,charindex('.',ip)+1)+1)+1) as integer) as part4, ip

from ipnumbers

order by part1, part2, part3, part4

This might work... Found examples of this in:

http://www.sqlbooks.ru/readarticle.aspx?part=09&file=addition09
or
http://www.databasejournal.com/features/mssql/article.php/3071531

Must work!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question