Convert factorized number to decimal in SQL Server

Question

I need to convert a number that gives decimal numbers in factorized format accurate to decimal places,

i.e. 11.16 - 11.5 in decimal system. This is due to the fact that 16 in the base 32 and 11.16 should be read as 11 + 16/32 = 11.5

I get 11.16 as a string, and I need to change it to 11.5 as a numeric value in a SQL Server 2005 database.

Is there any shorter way to do this, rather than split the strings, convert to numerical values, math, convert to string, concatenate and then convert to numeric?

Answer 1

As a function that does not require line splitting:

CREATE FUNCTION MyFunc
(
 @value varchar(10)
)
RETURNS float
AS
BEGIN
    declare @dValue float
    declare @fraction float

    Select @dvalue = convert(float, @value)
    Select @fraction = @dvalue  - Convert(int, @dvalue)
    Select @dvalue = (@dvalue - @fraction) + ((@fraction * 100) / 32)

 RETURN @dvalue
END

: , "8/32" 0,08, 0,8

"varchar (10)" , .

Answer 2

, , , :

• .

, , , [ ] . ,

UPDATE myOriginialTable
SET DecValue = F2D.DecVal
FROM myOriginalTable T
JOIN FactoredToDecimalTable F2D on T.FValue = F2D.FValue

, Factored Decimal ,

CREATE TABLE Factored32ToDec
( F32 DECIMAL decimal(12,2),
  Dec10 DECIMAL (12,2)
)

INSERT INTO Factored32ToDec VALUES (0, 0)
INSERT INTO Factored32ToDec VALUES (0.01, 1.0/32)
INSERT INTO Factored32ToDec VALUES (0.02, 2.0/32)
INSERT INTO Factored32ToDec VALUES (0.03, 3.0/32)
INSERT INTO Factored32ToDec VALUES (0.04, 4.0/32)
-- etc.  (24 rows omitted)
INSERT INTO Factored32ToDec VALUES (0.30, 30.0/32)
INSERT INTO Factored32ToDec VALUES (0.31, 31.0/32)

UPDATE myOriginialTable
SET DecValue = FLOOR(FValue) + F2D.Dec10
FROM myOriginalTable T
JOIN Factored32ToDec F2D ON F2D.F32 = (FValue - FLOOR(FValue))

, , [ ] Factored32ToDec , . , ... /.
, ( ), . , , , ( / , ..).

: FLOOR() , ; - ( ""... , ... , "X.1" - X + 1/32, "X.10" - X + 10/32). , , , , FLOOR. BTW, ( ".1" .01), .