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.
Add a comment
|
2 Answers
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.
Comments
Here is an option if you are using Postgre 9.3 or later:
SELECT SUM(s.num::int) AS sum
FROM your_table t, unnest(string_to_array(replace(t.col::text, '|', '^'), '^')) s(num);
Demo
But the better long term fix would be to avoid such SQL olympics, and instead to normalize your data. Ideally each number you want to sum should be in a separate record, which is what the above query actually does to generate the sum.
