We help IT Professionals succeed at work.

CONVERT varchar to integer contains commas display in GridView

Shaggy1
Shaggy1 asked
on
I have a VARCHAR field that contains numbers representing SQ.FT. I would like to convert that to an INT and round up or down the number by 500. The results will be displayed in a GridView.

example of the field:  10,000 or 145,500....

Would really appreciate the help. I've been trying to CAST, MATH.ROUND, etc, just can't get anything to work.

Comment
Watch Question

Commented:
There is no varchar datatype in c#, so i assume it is a string data type. If so, you can just replace the commas like this.

string mystring = "145,000";
mystring = mystring.Replace(",", "");

Author

Commented:
VARCHAR datatype is in SQL not C#. Writing a query in C#

Author

Commented:
So I'm retreiving the value in FIELD1 which is VARCHAR. When i run the query, i need to convert that to an INT so that i can ROUND it up or down..

Thanks!

Commented:
Hi,

Try this

SELECT CAST(ROUND(CAST(FIELD1 AS DECIMAL) / 500, 0) * 500 AS INT)
FROM yourtable

/peter
Vel EousResearch & Development Manager

Commented:
Once you have the value out of your database you could use the following, although it is quite ugly I must admit.
int converted = Convert.ToInt32(double.Parse("15,000"));

Open in new window

Commented:
Ahh, you need to remove the comma.

SELECT CAST(ROUND(CAST(REPLACE(FIELD1,',','') AS DECIMAL) / 500, 0) * 500 AS INT)
FROM yourtable

Author

Commented:
pivar,

If i already have the SELECT statement in place (SELECT DISTINCT) , can i still use the part CAST(ROUND(CAST etc..trying to add that in there and I am getting errors.

THANKS!

Commented:
Yes, you should be able to do that. What is the errordetails?
Top Expert 2011
Commented:
yes you should be able to  this gives you the value adjusted to the next 500...

select distinct ceiling(convert(int,replace(yourcolumn,',',''))/500.000)*500 as colName
  .....


post your current select statement if you still have issues...

(e.g. why are you using Distinct?)