Search code examples
sql-servertype-conversionaccounting

Convert a VARCHAR(n) to exact FLOAT

I have an accounting application. In my application, users can determine the number of decimal number, so I can't use decimal data type because it has fixed precision and scale.

According this topic, Use Float or Decimal for Accounting Application Dollar Amount?, never use float because it's just an approximation. So instead I use VARCHAR(n) as the data type for my column.

Now ,I want use SUM function on this column and I want the exact value of column. How can I do it?

Solution

  • You cannot have sum on CHAR data type in a normal way. You need to CONVERT it to a number and then do the calculations.

    My suggestion is to go for DECIMAL and if the end user can change the number of precision and scale, consider the maximum possible you can have and have SQL Server make the other digits as 0

    For example if you choose DECIMAL(19, 4) and end user selects to have just 2 scale, SQL Server make the other two digits as 0

    CREATE TABLE test(mny DECIMAL(18, 4))
INSERT INTO test VALUES (239428394.87)

SELECT * FROM test


OUTPUT
-----------------
239428394.8700

    Also you have an option to use MONEY data type.

    Comparing MONEY and DECIMAL

    If you want to use CHAR, try this:

    DECLARE @precision INT
        ,@scale INT
        ,@output VARCHAR(1000)
        ,@input CHAR(10) = '234234.453'

SELECT  @precision = LEN(LEFT(@input, CHARINDEX('.', @input) - 1)) + LEN(SUBSTRING(@input,CHARINDEX('.', @input) + 1, LEN(@input)))
        ,@scale = LEN(SUBSTRING(@input, CHARINDEX('.', @input) + 1, LEN(@input)))

SELECT @Output = 'SELECT SUM(CONVERT(DECIMAL(' + CONVERT(VARCHAR(100), @precision) + ', ' + CONVERT(VARCHAR(100), @scale) + '), ' + CONVERT(VARCHAR(100), @input) + '))'
SELECT @Output
EXEC (@Output)

    You can dynamically get precision and scale of each number and make a dynamic query