I have a column of varchar() type, value can be any string, is it possible to find sum of all rows that can be cast to numeric?
value
----
| 3 |
| 2 2|
| as |
| a1 |
| !2@|
| 0.5|
----
3.5
Add a comment
|
2 Answers
You can use a case expression and convert:
select (case when value ~ '^[0-9]+[.]?[0-9]*$'
then value::numeric
else 0
end)
from t;
Here is a db<>fiddle.
2 Comments
The Impaler
You missed
SUM().
Gordon Linoff
@TheImpaler . . . I also missed that 0.5 isn't an integer. ;)
May be:
with tb as(
select '3' as v union all
select '2 2' as v union all
select 'as' as v union all
select 'a1' as v union all
select '!2' as v union all
select '0.5'
)
select sum(case when v ~ '^[0-9\.]+$'
then v::numeric
else 0
end) as result
from tb;
Comments
