converting fractions to decimal

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)

converting fractions to decimal

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-11-11 : 08:49:21

Hi All,

could anyone help me on converting a number like '0.36773E -01' to decimal number please?
I want 16 decimal places to be shown.

I tried something like this:


DECLARE @FRACTION VARCHAR(50)
SET @FRACTION = '0.36773E-01'
SELECT	CAST(@FRACTION AS DECIMAL(20,16))

Result:
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.

umertahir
Posting Yak Master

154 Posts

Posted - 2009-11-11 : 09:13:31

I found this code which did work for me:


CREATE FUNCTION dbo.fnResolveFractionals
(
            @data VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
            RETURN      CASE
                                     WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)
                                     WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)
                                     ELSE NULL
                         END
END
GO

To test if it worked:


declare	@varout DECIMAL(20,16)
select	@varout = dbo.fnResolveFractionals('0.36773E-7')
print @varout

Result:
0.0000000367730000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-11 : 09:35:09

[code]
declare @s varchar(100),
@f decimal(20,16)

select @s = '0.36773E-7'

select @f = convert(float, @s)

print @f
[/code]

KH
[spoiler]Time is always against us[/spoiler]

umertahir
Posting Yak Master

154 Posts

Posted - 2009-11-11 : 10:11:39

..and your point is?

quote:
Originally posted by khtan

declare @s varchar(100),
@f decimal(20,16)

select @s = '0.36773E-7'

select @f = convert(float, @s)

print @f

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-11 : 13:21:17

quote:
Originally posted by umertahir

..and your point is?
<snip>

I'm guessing the point is you do not need a custom function to do this conversion.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-11 : 20:53:07

exactly. Just need to convert to a float variable and then the rest is presentation issue.

KH
[spoiler]Time is always against us[/spoiler]

umertahir
Posting Yak Master

154 Posts

Posted - 2009-11-12 : 04:33:04

O right..even simpler. I guess the CASE statement in the function deals with the fraction numbers with '/' in them. Anyway, thanks guys!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-12 : 08:10:26

I guess you found the code here
http://www.sqlservercentral.com/Forums/Topic619023-149-1.aspx

N 56°04'39.26"
E 12°55'05.63"

umertahir
Posting Yak Master

154 Posts

Posted - 2009-11-12 : 08:19:04

Nah..I prefer looking on SQLTeam.com forums for quick solutions and I got the code from here:

[url]http://weblogs.sqlteam.com/peterl/archive/2008/12/15/One-way-to-resolve-and-calculate-fractional-strings.aspx[/url]

Good investigations though, sql detective.

quote:
Originally posted by Peso

I guess you found the code here
http://www.sqlservercentral.com/Forums/Topic619023-149-1.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources