我在Vertica数据库中有一个表,其列ip类型为string,同时包含ipv4和ipv6格式。我需要将ip的字符串表示转换为数字,例如这里所做的-https://www.ipaddressguide.com/ipv6-to-decimal
为了处理ipv4,Vertica内置了函数: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
我正在寻找将ipv6地址转换为数字的相同功能:IP地址2001:569:7c0e:5700:1133:9bd:232f:9c78等于4254059793137440240240310283203222256248
好吧,您可以将字符串'0xFF'
硬转换为NUMERIC(3)
,得到255。
考虑到这一点-
-
对于用冒号分隔的每个字符串标记
-
用
'0'
将字符串标记向左填充到4 的长度 -
将由此获得的8个4-卡环连接起来
-
用
'0x'
预处理 -
并且全部硬铸造到CCD_ 5。
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