2

I want to format a number to display decimal places according to a configuration value specified in another table. If the configured value is NULL, the number of decimal places should default to 2 (hardcoded here) The following statement works fine except that for values like 3.50000 which returns 3.5 even when the configured value for 'NumberOfDecimalPlaces' is 2 or 3. I get that it is the desired behavior for ROUND()

ROUND([ActualValue], COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)) [FormattedActualValue]

As an alternative, I tried CONVERT AND CAST.

SELECT CAST ([ActualValue] AS NUMERIC(16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2))

SELECT CONVERT(DECIMAL (16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)), [ActualValue])

Both of which err to Incorrect syntax near the keyword 'COALESCE'. I understand that the second parameter to the datatype definition is not nullable and hence the error. What are my options? How can I achieve this with minimum performance overhead?

  • 1
    You'll probably need to use dynamic SQL for this. – Erik Darling Feb 15 '17 at 13:32
4

The STR function seems to be what you're looking for:

DECLARE @NumberOfDecimalPlaces tinyint = 3;
DECLARE @ActualValue decimal(16,5) =  3.50

SELECT FormattedActualValue = 
    STR(
        @ActualValue, 
        CAST(@ActualValue / 10 AS int) + @NumberOfDecimalPlaces + 2,
        COALESCE(@NumberOfDecimalPlaces,2)
    )
  • 1
    Nice, I guess I'd long forgotten that STR could use parameters there (wouldn't it be nice if all built-ins did this?). I shy away from STR generally because the output has to be a string and because you have to add that unintuitive / convoluted stuff to get it to round properly (like we do in math). So I guess it's pick your poison between that and dynamic SQL. – Aaron Bertrand Feb 15 '17 at 15:04
  • 1
    @AaronBertrand Regarding "all built-ins should support this" - hell, yeah! I wish! Unfortunately, I suppose it would be a devastating change to the parser. Regarding "output has to be a string": I think that's what the OP wants here, contrary to what is usually asked. – spaghettidba Feb 15 '17 at 15:10
  • @spaghettidba Thanks for the STR(). Couldn't have thought of it easily. I am aiming at creating a view and this looks like a lot of performance overhead. I need to weigh my options with both dynamic SQL and STR(). What do you suggest? – lohiarahul Feb 16 '17 at 4:08
  • Dynamic SQL will generate a view definition only on the basis of current values and that would return erroneous results if the configured value of the decimal place would change later. I guess using STR() is my only option. Thanks. – lohiarahul Feb 16 '17 at 4:51
3

You can't use parameters/variables for precision, so you'll need to use dynamic SQL for this. Performance overhead here is relatively close to zero.

DECLARE @NumberOfDecimalPlaces tinyint;

SET @NumberOfDecimalPlaces = 3;

DECLARE @sql nvarchar(max) = N'SELECT FormattedActualValue = 
  CONVERT(DECIMAL(16, $decimal_places$), 13.5457);';

SET @sql = REPLACE(@sql, N'$decimal_places$', COALESCE(@NumberOfDecimalPlaces,2));

EXEC sys.sp_executesql @sql;

After seeing Gianluca's answer I started to play with something else, which may or may not suit your needs (depending on whether you want trailing zeros when @DecimalPlaces > (decimal places in original value):

DECLARE @DecimalPlaces tinyint       = 5,
        @ActualValue   decimal(16,5) = 3.561735;

SELECT LTRIM(STR(ROUND(@ActualValue,@DecimalPlaces),20,@DecimalPlaces));
  • Yes. I do need trailing zeros when @DecimalPlaces > (decimal places in original value). Otherwise ROUND() would have sufficed. – lohiarahul Feb 16 '17 at 4:05
0

The FORMAT function accepts a wide variety of input types. The output can be formatted to the culture & locale of the user. It produces an nvarchar result.

0

This is a slight alteration to spaghettidba's answer. When specifying the number of digits to show, you need to do something other than devide the value by 10:

The STR function accepts 3 arguments:

STR( [Value],[Total Number of Didgits],[Decimal Places])

The correct calculation of [Total Number of Didgits] should be something like:

 LEN(CAST(ROUND([Value], 0) AS INT)) + [Decimal Places] + 1
 /* + 1 for the decimal point itself */

this will get the number of digits before the decimal place. E.g.

DECLARE @NumberOfDecimalPlaces TINYINT = 3;
DECLARE @ActualValue DECIMAL(16, 5) =  .005643

SELECT FormattedActualValue = STR(@ActualValue, LEN(CAST(ROUND(@ActualValue, 0) AS INT)) + @NumberOfDecimalPlaces + 1, @NumberOfDecimalPlaces)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.