converting factored number to decimal in SQL Server

Question

I need to convert a number which gives decimals in factored format in to decimals,

i.e. 11.16 is 11.5 in decimal. This is because 16 is in base of 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 numeric in a SQL Server 2005 database.

Any shorter way of doing this rather than splitting strings,converting to numerics, maths, convert to string, concatenate and then convert to numeric?

Answer 1

As a function that doesn't require string-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

NOTE: This assumes that '8/32' is represented as 0.08, not 0.8

You should adjust the "varchar(10)" to match what you actually need.

Answer 2

Typically the kind of data described in the question comes from financial data of various kind and such data often shares two characteristics:

• many millions of rows, and
• relatively few distinct values.

A possible strategy for bulk converting some of this data, may be to establish a list of distinct values found in the data, to convert these values, storing value and result in a [relatively small] look-up table. Such table can then be indexed and used in an update query similar to the following

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

Whether the above trick with a look-up table is used, the conversion from Factored to Decimal can also be done by looking up the fractional part of the input value in a table such as the following

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))

Essentially this idea assumes that the lookup in the [obviously cached] Factored32ToDec table will be faster than the corresponding division operation. I'm not sure that is the case... this would need to be profiled/tested.
On the other hand, I've seen the first look-up approach (for all distinct values), in use. This can be a quite efficient way of solving the problem, but the mileage you get out this varies depending one the total number of rows, and on the number of distinct values (as well as the presence/absence of various indexes, and other considerations such as update of a single column vs. two separate columns etc.).

Note: the user of FLOOR() implies that the value at hand is numeric; in some cases the input data is merely a string (which is "useful" ... don't ask me why... to store the values without a leading zero, i.e "X.1" is X + 1/32 whereas "X.10" is X + 10/32). Depending on the situation, one may therefore need to split the string at the decimal point rather than using FLOOR. BTW, when coded as strings (".1" vs. .01), the small look-up table approach saves a lot of tests compared with an arithmetic approach.