Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

convert to 0.00

93.06  
93.01  
924,95
355.24  
355,96
355,41
2.245,82
2.074,95
2.034,95  
2,579.70
1.284,81  
1.284,81  
1.275,81  

want to find sum of this varchar column
there are
,
and
both
. and ,
large numbers

I would like to convert all to 0.00
0
rgb192
Asked:
rgb192
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
mkobrinCommented:
sum(convert(tofloat, columnName))

Should do it for you or you can use a convert to decomal with the required settings as well.
0
 
rgb192Author Commented:
Msg 243, Level 16, State 1, Line 2
Type tofloat is not a defined system type.
0
 
mkobrinCommented:
Sorry (Using C# syntax)

Try sum(convert(float, columnName))
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
rgb192Author Commented:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
0
 
Bhavesh ShahLead AnalysistCommented:
0
 
Erick37Commented:
The assumption here is that there will always be 2 decimal places in the numbers.
The following removes all '.' and ',' and then converts to a number, divides by 100 to get the decimal back in the right place and then sums the column.

SELECT
      CAST(SUM(CONVERT(NUMERIC(19,2), REPLACE(REPLACE(YourColumn,',',''),'.','')) / 100) AS NUMERIC(19,2))
FROM yourtable
0
 
Erick37Commented:
OK, this will take care of the case when there is a mix of number with 2 decimal places and numbers without decimal places e.g. 123.45 and 123

SELECT
      CAST(
            SUM( CASE PATINDEX('__[,|.]%',REVERSE(YourColumn))
                  WHEN 1 THEN CONVERT(NUMERIC(19,2), REPLACE(REPLACE(YourColumn,',',''),'.','')) / 100
                  ELSE CONVERT(NUMERIC(19,2), REPLACE(REPLACE(YourColumn,',',''),'.',''))
                  END)
      AS NUMERIC(19,2)) AS Total
FROM @YourTable
0
 
parthmalhanCommented:
Try the following Code:

select sum((convert(money,columnname))) from tablename
0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now