how to calculate sum of numbers in a string in postgresql

Question

1^1^1^5^1|1^1|1^1

Is there a simple way to add all above numbers in postgresql. I would like to write a function that takes above string and outputs 13.

Answer 1

You could use the function regexp_split_to_table

SELECT sum(s::int) 
  FROM regexp_split_to_table('1^1^1^5^1|1^1|1^1','\D') as s

Here you use the regex \D to split on everything that is not a digit, leaving you with the groups of digits (read more about postgres regex), which you then sum as integers.