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

# 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
• 3
• 2
• 2
• +2
3 Solutions

Commented:
sum(convert(tofloat, columnName))

Should do it for you or you can use a convert to decomal with the required settings as well.
0

Author Commented:
Msg 243, Level 16, State 1, Line 2
Type tofloat is not a defined system type.
0

Commented:
Sorry (Using C# syntax)

Try sum(convert(float, columnName))
0

Author Commented:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
0

0

Commented:
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

Commented:
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

Commented:
Try the following Code:

select sum((convert(money,columnname))) from tablename
0

Author 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.