Convert a VARCHAR(n) to exact FLOAT

Question

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?

Check the convert method, you can do something like: convert(float, yourVarcharColumn), maybe it will give you a start. — gabriel, Mar 30 '15 at 06:15
please read the question i want convert VARCHAR to float and he want convert float to VARCHAR — ali, Mar 30 '15 at 06:41
You are right, I added the wrong URL: http://raresql.com/2013/04/26/sql-server-how-to-convert-varchar-to-float/ — gabriel, Mar 30 '15 at 06:45
You could use the CLR and load the values to sum into a BigRational implementation. (Or write it yourself. Summing decimals in strings isn't too difficult) — Millie Smith, Mar 31 '15 at 01:04
SQL Server supports maximum precision and scale 38 for [`decimal type`](https://msdn.microsoft.com/en-us/library/ms187746.aspx). If your numbers can have more than 38 significant digits, then the built-in `SUM` can't be used without the loss of precision. Overall, this requirement looks strange to me. Usually in accounting it is known how many decimal places should be used. At least, usually it is known how many decimal places should be reserved. And usually it is 2 or 4. That's why there is a type called [`money`](https://msdn.microsoft.com/en-us/library/ms179882.aspx). — Vladimir Baranov, Mar 31 '15 at 02:03

score 1 · Accepted Answer · edited May 23 '17 at 12:28

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

thanks for your answer I know i cannot have sum on CHAR but i want some casting on my query. — ali, Mar 30 '15 at 06:16
You can `SUM(CONVERT(DECIMAL(18, 4), value))` but I strongly recommend you to not to use CHAR — sqluser, Mar 30 '15 at 06:25
but maybe the scale bigger than 4. my problem is the scale is dynamic and user can determine scale now I don't know what should i do? — ali, Mar 30 '15 at 06:33
thanks for your Ideas . i use Entity framework and I don't think about a dynamic query . — ali, Mar 31 '15 at 03:29

Convert a VARCHAR(n) to exact FLOAT

1 Answers1