Not correct IP sorting in DBGRID

Posted on 2007-07-20
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 :/
Question by:selas
    LVL 21

    Expert Comment

    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

    LVL 4

    Expert Comment

    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.


    Author Comment

    How to do this with SQL ???
    select * from table order by IP
    LVL 4

    Expert Comment

    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 ( Then if you want to show ip numbers like instead of you can include a calculated field and reformat the string to its original value with Delphi function.
    LVL 4

    Accepted Solution

    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:

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

    Author Comment

    yes IP is a string field
    i use microsoft SQL server
    and IP adresses stored as
    LVL 4

    Expert Comment

    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,
    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!!!
    LVL 4

    Expert Comment

    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:

    Must work!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
    This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now