How to remove Decimal point

Question

How to remove Decimal point

dheer

Ten Centuries

Points: 1275
More actions
February 22, 2010 at 9:34 am

#217004

Hi i have column with data type as Varchar.The coloumns contains the numbers with decimal point in between for ex: 789.24,7892.5 etc. How can i remove those decimal points. I want the result set as
78924,78925.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

dheer (2/22/2010)
Hi i have column with data type as Varchar.The coloumns contains the numbers with decimal point in between for ex: 789.24,7892.5 etc. How can i remove those decimal points. I want the result set as
78924,78925.
Thanks in advance.

you picked a great example; in your presentation, if you removed the decimal, how do you know that

78925 is not one of these values: 7892.5 ,789.25 or 78925? don't you really need to round them up or down to the integer value, instead of stripping decimal? or multiply by 100 to get consistent values?

anyway you could do it like this:

SELECT REPLACE(CONVERT(varchar, 7892.5),'.','')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dheer Ten Centuries Points: 1275 More actions · Answer 2

Sorry for not mentioning properly....The number is a ..I m not rounding it off...i just want to remove that decimal....for ex if the number 789.90 .....after removing decimal it should be 78990 not 7899

Teno Newbie Points: 1 More actions · Answer 3

Lowell !!!

this SELECT REPLACE(CONVERT(varchar, 7892.5),'.','')

has been very helpfull for me!!

Thanks!!

Teno.

kaj SSCommitted Points: 1607 More actions · Answer 4

If you simply want to get rid of the decimal point, you can use the REPLACE funtion to replace it with a zero-length string, i.e.

SELECT REPLACE(yourColumn, '.' ,'') AS yourColumn

I'm guessing it's a primitive $ -> ¢ conversion?

But as Lowell has already pointed out, you'd need to be 100% sure that the source varchar value always has a consistent number of decimals, i.e. 100 is represented as 100.00 and 100.1 is represented as 100.10. Otherwise you will get a misrepresented value by just removing the decimal point.

So it would be a better and more robust solution to do an intermediate conversion to a decimal type, multiply by 100, and convert back to varchar (if that is the type you ultimately aim for), i.e. something like this:

SELECT CONVERT(varchar(20),CONVERT(int,TRY_CONVERT(dec(15,2),yourColumn)*100)) AS yourColumn

This reply was modified 3 years, 4 months ago by kaj. Reason: Little typo