[RESOLVED] replace 5th decimal in numeric value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[RESOLVED] replace 5th decimal in numeric value

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-03-08 : 12:00:44

I need to be able to update the 5th decimal in a numeric value: 298.619390103 --> 298.619300103
So far I can determine the value of the 5th decimal in my value by


select CAST([NumValue] * 100000  % 10 as int)

If my Value is 278.619390103 the query would return '9' as the value for the 5th decimal but if I use it in a REPLACE function all my '9's would be replaced.


SELECT REPLACE([NumValue],CAST([NumValue] * 100000  % 10 as int),0)

RESULT: 208.610300103

Any assistance would be appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 12:43:07

Here is an approach:
select
convert(varchar(30),298.619390103) as orig,
stuff(convert(varchar(30),298.619390103),charindex('.',convert(varchar(30),298.619390103))+5,1,'0') as new

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-08 : 14:13:25

Here's another approach

declare @num numeric (18,15)
set @num = 298.619390103 --> 298.619300103
select convert(varchar(30),floor(@num))
+convert(float,stuff(convert(varchar(30),@num - floor(@num)),7,1,'0'))

Jim

Everyday I learn something that somebody else already knew

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-03-09 : 12:53:11

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources