如何在PostgreSQL中将冒号分隔的IPv6十六进制字符串转为十进制

我来帮你搞定这个IPv6转NUMERIC的需求！首先咱们先理清楚问题：你要把2001:200:101:ffff:ffff:ffff:ffff:ffff这个IPv6地址转换成数值42540528727106952925351778646877011967，核心是把128位的十六进制IPv6字符串转成PostgreSQL的NUMERIC类型（因为普通整数类型存不下这么大的值）。

方法一：补全并使用自定义转换函数 #

你之前用的hex_to_int函数思路是对的，我把它补全并完善，确保能处理大小写十六进制字符，还会抛出无效字符的异常：

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS numeric AS $$
DECLARE
  result NUMERIC := 0;
  i integer;
  len integer;
  hexchar varchar;
BEGIN
  len := length(hexval);
  FOR i IN 1..len LOOP
    hexchar := substr(hexval, i, 1);
    result := result * 16 + CASE hexchar
      WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3 WHEN '4' THEN 4
      WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7 WHEN '8' THEN 8 WHEN '9' THEN 9
      WHEN 'a' THEN 10 WHEN 'b' THEN 11 WHEN 'c' THEN 12 WHEN 'd' THEN 13 WHEN 'e' THEN 14 WHEN 'f' THEN 15
      WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15
      ELSE RAISE EXCEPTION 'Invalid hex character: %', hexchar;
    END;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

然后我们需要先把IPv6地址转换成无冒号且补全前导零的十六进制字符串（IPv6允许省略前导零，比如200其实是0200，必须补全才能保证转换正确），可以写个小辅助函数：

CREATE OR REPLACE FUNCTION ipv6_to_hex(ipv6_addr inet) RETURNS varchar AS $$
BEGIN
  RETURN regexp_replace(lower(host(ipv6_addr)), ':', '', 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

现在直接调用这两个函数就能得到你要的结果：

SELECT hex_to_int(ipv6_to_hex('2001:200:101:ffff:ffff:ffff:ffff:ffff'::inet));

执行后会返回你期望的42540528727106952925351778646877011967。

方法二：用PostgreSQL自带函数更简洁 #

其实不需要自定义函数，利用to_number就能直接转换，只要先把IPv6转成标准的无冒号十六进制字符串：

SELECT to_number(
  regexp_replace(host('2001:200:101:ffff:ffff:ffff:ffff:ffff'::inet), ':', '', 'g'),
  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
);

这个查询同样能得到目标数值，而且更简洁。

关键注意点 #

你之前提到的输入字符串2001200101ffffffffffffffffffff是有问题的——原IPv6的第二段200应该补全为0200，所以正确的无冒号字符串是200102000101ffffffffffffffffffff，如果漏了前导零，转换结果就会出错，一定要注意这一点！

内容的提问来源于stack exchange，提问作者antosr7