WITH data_t AS ( SELECT 1 pk, '1|2|3|4' v UNION ALL SELECT 2, '3|4|5|6' ) , copy_t AS ( SELECT 1 lv UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) SELECT pk , v , SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(v, '|', lv), '|', -1)) x FROM data_t a INNER JOIN copy_t c WHERE lv <= LENGTH(v) - LENGTH(REPLACE(v, '|', '')) + 1 GROUP BY pk, v ;