How can check in DB table ip address in rage?

Question

I have entity

public class CrmActionLogIp implements Serializable {
    @Id
    private long id;
    @Column(name = "ip_start")
    private String ipStart;
    @Column(name = "ip_end")
    private String ipEnd;
    @Column(name = "office_name")
    private String officeName;

I save ip ti this table. if officeName has 1 ip address I save ipStart = ipEnd if officeName has range of ips I save ipStart = range start and ipEnd = range end. For example:

id   ipStart       ipEnd          officeName
0    127.0.0.1     127.0.0.1      local office
1    129.127.0.0   129.127.0.12   test office
2    132.127.0.11   132.127.0.22   test office2

And How can I get officeName from DB by ip? For exaple I have 129.127.0.5 it is between 129.127.0.0 AND 129.127.0.12 so I get officeName = test office.

Which dbms are you using? (The answer will probably take advantage of product specific functionality.) — jarlh, Jan 27 '17 at 08:40
please take a look at the answer here: http://stackoverflow.com/questions/14994142/storing-an-ip-address-in-a-oracle-sql-table when you store ip as hexadecimal value, then you can compare values — Tobias Otto, Jan 27 '17 at 08:43

score 0 · Answer 1 · edited May 23 '17 at 12:01

I'd suggest to add two additional columns, i.e. ipStartNum and ipEndNum, where you convert the ip string value to a number according, for example, using the following formula (this formula is taken from this SO answer, please upvote if it helps you). Then, when searching for the office based on a particular ip, transform this ip into such a number and do a BETWEEN-query:

CREATE TABLE actionlog
    ("id" int, "ipStart" varchar2(12), "ipEnd" varchar2(12), "officeName" varchar2(12), "ipStartNum" number, "ipEndNum" number)
\\

INSERT ALL 
    INTO actionlog ("id", "ipStart", "ipEnd", "officeName")
         VALUES (0, '127.0.0.1', '127.0.0.1', 'local office')
    INTO actionlog ("id", "ipStart", "ipEnd", "officeName")
         VALUES (1, '129.127.0.0', '129.127.0.12', 'test office')
    INTO actionlog ("id", "ipStart", "ipEnd", "officeName")
         VALUES (2, '132.127.0.11', '132.127.0.22', 'test office2')
SELECT * FROM dual
\\

update actionlog set "ipStartNum" = to_number(regexp_substr("ipStart", '\d+', 1, 1)) * 16777216 +
           to_number(regexp_substr("ipStart", '\d+', 1, 2)) * 65536 +
           to_number(regexp_substr("ipStart", '\d+', 1, 3)) * 256 +
           to_number(regexp_substr("ipStart", '\d+', 1, 4))
\\

update actionlog set "ipEndNum" = to_number(regexp_substr("ipEnd", '\d+', 1, 1)) * 16777216 +
           to_number(regexp_substr("ipEnd", '\d+', 1, 2)) * 65536 +
           to_number(regexp_substr("ipEnd", '\d+', 1, 3)) * 256 +
           to_number(regexp_substr("ipEnd", '\d+', 1, 4))
\\

select * from actionlog
where to_number(regexp_substr('129.127.0.5', '\d+', 1, 1)) * 16777216 +
           to_number(regexp_substr('129.127.0.5', '\d+', 1, 2)) * 65536 +
           to_number(regexp_substr('129.127.0.5', '\d+', 1, 3)) * 256 +
           to_number(regexp_substr('129.127.0.5', '\d+', 1, 4)) between "ipStartNum" and "ipEndNum"
\\

Note: to keep values of ipStartNum and ipEndNum up to date, you could probably use calculated columns, triggers, or java-application based logic as well. See solution based on calculated columns below:

CREATE TABLE actionlog
    ("id" int, "ipStart" varchar2(12), "ipEnd" varchar2(12), "officeName" varchar2(12),
     "ipStartNum"  as (to_number(regexp_substr("ipStart", '\d+', 1, 1)) * 16777216 +
           to_number(regexp_substr("ipStart", '\d+', 1, 2)) * 65536 +
           to_number(regexp_substr("ipStart", '\d+', 1, 3)) * 256 +
           to_number(regexp_substr("ipStart", '\d+', 1, 4))),
     "ipEndNum" as (to_number(regexp_substr("ipEnd", '\d+', 1, 1)) * 16777216 +
           to_number(regexp_substr("ipEnd", '\d+', 1, 2)) * 65536 +
           to_number(regexp_substr("ipEnd", '\d+', 1, 3)) * 256 +
           to_number(regexp_substr("ipEnd", '\d+', 1, 4))))

score 0 · Accepted Answer · answered Feb 08 '17 at 06:23

I write this util method

public static long ipToLong(String ip) {
        String[] addrArray = ip.split("\\.");

        long ipDecimal = 0;

        for (int i = 0; i < addrArray.length; i++) {

            int power = 3 - i;
            ipDecimal += ((Integer.parseInt(addrArray[i]) % 256 * Math.pow(256, power)));
        }
        return ipDecimal;
    }

and save 4 columns ipStart ipEnd ipStartLond ipEndLog

How can check in DB table ip address in rage?

2 Answers2