By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,143 Members | 708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,143 IT Pros & Developers. It's quick & easy.

How to sum a field that numbers in varchar

P: 2
I want to calculate a sum from a field that has varchar fields, the values are in this format ######,###.
The numbers before the comma can vary and the ones after the comma are allways 3. I get these values from a table from other database (so modifying that table is no option).

I know that I can't do a sum on a varchar, so i have to convert it
i tried different cast and convert statements but i allways get a error that says that the conversion failed

How can I do this sqlserver itself?
May 31 '10 #1
Share this Question
Share on Google+
3 Replies

Delerna
Expert 100+
P: 1,134
you can't convert it because of the comma.
You can remove the comma with replace() before converting it

Expand|Select|Wrap|Line Numbers
  1.    convert(  decimal(18,3) , replace( thefieldname , ',' , '' )  )
  2.  
May 31 '10 #2

P: 2
Delerna,
Thanks for the quick response.
When i do this i get the number as a integer so the numbers behind the comma are now in front of it.

How can i solve this?
I tried to divide by 1000 but stll no numbers behind comma.
Jun 1 '10 #3

Delerna
Expert 100+
P: 1,134
I take it then that the comma is being used to represent a decimal point
in that case

replace( thefieldname , ',' , '.' )

should solve it
Jun 2 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.