How to convert address in IPv6 to number using Vertica

Question

I have a table in Vertica db with column ip of type string that holds both formats of ipv4 and ipv6. I need to transform the string representation of the ip to a number as it is done for example here - https://www.ipaddressguide.com/ipv6-to-decimal

To deal with ipv4 Vertica has built in function: inet_aton() https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/INET_ATON.htm

SELECT INET_ATON('1.2.3.4');
 inet_aton 
-----------
  16909060

I'm looking for the same functionality to convert ipv6 address to number: IP address 2001:569:7c0e:5700:1133:9bbd:232f:9c78 is equal to 42540597931374402043102832032222256248

Answer 1

Well - you can hard-cast the string '0xFF' to NUMERIC(3), to get 255.

With that in mind -

• for each string token separated by colon

• left-pad the string token with '0' to a length of 4

• concatenate the 8 thus obtained 4-char-strings

• prepend them with '0x'

• and hard-cast all to NUMERIC(38,0).

WITH
ipv6(ipv6) AS (
            SELECT '2001:0569:7c0e:5700:1133:9bbd:232f:9c78'
  UNION ALL SELECT '2001:569:7c0e:5700:1133:9bbd:232f:9c78'
)
SELECT 
  ipv6
, (
     '0x'
   || LPAD(SPLIT_PART(ipv6,':',1 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',2 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',3 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',4 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',5 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',6 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',7 ),4,'0')
   || LPAD(SPLIT_PART(ipv6,':',8 ),4,'0')
  )::NUMERIC(38,0) AS ipv6_2_int
, '0xff'::NUMERIC(3) AS hex_255
FROM ipv6;
-- out                   ipv6                   |               ipv6_2_int               | hex_255 
-- out -----------------------------------------+----------------------------------------+---------
-- out  2001:0569:7c0e:5700:1133:9bbd:232f:9c78 | 42540597931374402043102832032222256248 |     255
-- out  2001:569:7c0e:5700:1133:9bbd:232f:9c78  | 42540597931374402043102832032222256248 |     255