CODEWITHSUNDEEP
mysql
Alex Borsody
Alex Borsody

Reputation: 2040

Sum varchar field in SQL

I have a Varchar field for gross, the field has something like $1,000, $190, $2,900

I need to sum this column, should I alter the column to INT? and then use a regex to remove the $? Any suggestions.

Upvotes: 0

Views: 132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You should fix the database so numbers are stored as numbers, not as strings. But, to do the sum, you can try:

select sum(replace(replace(gross, ',', ''), '$', '') + 0)

This will convert your examples to a number and add them up. If something is in a different format, it will silently produce a 0 for that value.

By the way, "gross" is an apt name for a field varchar field containing a number.

Upvotes: 3

Related Questions