CREATE TABLE ipv6 (
address VARCHAR2(40)
);

INSERT INTO ipv6 VALUES('0:0:0:0:0:0:0:0');
INSERT INTO ipv6 VALUES('0:10:0:10:0:0:0:0');
INSERT INTO ipv6 VALUES('0:0:10:0:10:0:10:0');
INSERT INTO ipv6 VALUES('0:10:0:0:10:0:0:10');
INSERT INTO ipv6 VALUES('0:0:10:0:0:1011:101:1011');
INSERT INTO ipv6 VALUES('10:20:30:40:50:60:70:8');

WITH x_addresses AS (
  SELECT ROWNUM rn, address,
  REGEXP_REPLACE(address, '[1-9a-zA-Z][0-9a-zA-Z]*', 'X') x_ip
  FROM ipv6
),
chains AS (
  SELECT rn, address, x_ip, occurence,
  REGEXP_SUBSTR(x_ip, '(:?0:?)+', 1, occurence) chain
  FROM x_addresses CROSS JOIN
  (SELECT LEVEL occurence FROM dual CONNECT BY LEVEL <= 8)
  WHERE REGEXP_SUBSTR(x_ip, '(:?0:?)+', 1, occurence) IS NOT NULL OR occurence = 1
  ORDER BY rn, REGEXP_COUNT(chain, '0') DESC, occurence
),
max_chains AS (
SELECT rn, address, x_ip, occurence, chain
FROM (
  SELECT rn, address, x_ip, occurence, chain,
  ROW_NUMBER() OVER (PARTITION BY rn ORDER BY REGEXP_COUNT(chain, '0') DESC, occurence) grnum
  FROM chains)
WHERE grnum = 1
)

SELECT address,
REGEXP_REPLACE(address, chain, '::', 1, 1) normalized_address
FROM max_chains;