Format Decimal column

Question

Hi.
I am trying to format a column, so that value is displayed in 30 characters before the decimal and 4 places after the decimal.

Example: +000000000000000000000002500000.0000 ( + or - depending on the value ) .

I tried the below:

select CAST(ISNULL(NULLIF(columnName, ''),0) AS DECIMAL(30,4)) from table.

But this does result in the patter required. How to format this , as shown in the example?
Thanks

Answer 1

Try this:

select format( cast(isnull(nullif(columnname, ''),0) as decimal(34,4)), '+000000000000000000000000000000.0000;-000000000000000000000000000000.0000')

Answer 2

Hi

Best Regards.
Please remember to mark the replies as answers if they help.

Answer 3

Here is an alternate solution:

DECLARE @d decimal(30,4) = 2221.2346
SELECT IIF(@d >= 0, '+', '-') + replace(str(abs(@d), 35, 4), ' ', '0')

This could perform better if the data set is large. The format function is implemented in the CLR and that context switch comes with a price.

Answer 4

Note that the (working) suggestions you got in this thread returns a string data type, not a numeric data type. You can't return a numeric type and enforce some specific formatting, it is the client app the decides on how to represent your data. Se as soon as we talk about some specific format you either have to find a suitable type where your client app give you the presentation you want (not possible for your case) or return it as a string.