Solved

# convert to 0.00

Posted on 2010-11-11
285 Views
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
Question by:rgb192
• 3
• 2
• 2
• +2

LVL 7

Accepted Solution

mkobrin earned 167 total points
ID: 34111178
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 Comment

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

LVL 7

Expert Comment

ID: 34111286
Sorry (Using C# syntax)

Try sum(convert(float, columnName))
0

Author Comment

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

LVL 19

Expert Comment

ID: 34111644
0

LVL 32

Expert Comment

ID: 34111671
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

LVL 32

Assisted Solution

Erick37 earned 167 total points
ID: 34111851
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

LVL 4

Assisted Solution

parthmalhan earned 166 total points
ID: 34144509
Try the following Code:

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

Author Closing Comment

ID: 34220301
thanks
0

## Featured Post

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…