IP address integer to string and string to integer functions

[cirosantilli]

It would be good to add functions that convert IPv4 and IPv6 address to/from strings.

A use case I had is you get a .csv file with string IPs, but it is much more efficient to handle them as integer in DB.

E.g.:

sqlite3 :memory: '.load ./ip' "SELECT printf('%08x', str2ipv4('1.128.2.10'))"

would output:

0180020a

At: https://stackoverflow.com/questions/7638238/sqlite-ip-address-storage/76520885#76520885 I show an implementation of str -> int, but would need to add the inverse and IPv6 support too.

Lazy to make a clean pull request here, but it's an idea.

[terefang]

let me also suggest

functions

ipmask(str)

sqlite> select ipmask('192.168.16.12/24');
255.255.255.0

ipmasktolen(str)

sqlite> select ipmasktolen('255.255.254.0');
23

ipmatch(pattern, needle)

sqlite> select ipmatch('192.168.*','192.168.16.12');
true
sqlite> select ipmatch('192.168.*.12','192.168.16.12');
true
sqlite> select ipmatch('192.*.*.12','192.168.16.12');
true
sqlite> select ipmatch('192.*','192.168.16.12');
true
sqlite> select ipmatch('*.12','192.168.16.12');
true
sqlite> select ipmatch('192.168.1-17.12','192.168.16.12');
true
sqlite> select ipmatch('192.168.1-14.12','192.168.16.12');
false
sqlite> select ipmatch('192.150-168.1-17.11-12','192.168.16.12');
true
sqlite> select ipmatch('192.150-168.*.10','192.168.16.12');
false

and the mysql functions

• INET_ATON() | Return the numeric value of an IP address
• INET_NTOA() | Return the IP address from a numeric value
• INET6_ATON() | Return the numeric value of an IPv6 address
• INET6_NTOA() | Return the IPv6 address from a numeric value
• IS_IPV4() | Whether argument is an IPv4 address
• IS_IPV4_COMPAT() | Whether argument is an IPv4-compatible address
• IS_IPV4_MAPPED() | Whether argument is an IPv4-mapped address
• IS_IPV6() | Whether argument is an IPv6 address

and the logical derivatives

• INET_PTON() | Return the numeric value of an IP4/6 address
• INET_NTOP() | Return the IP4/6 address from a numeric value

[nalgeon]

Sorry, I have no plans to add new features at this time.