Format the number of decimals based on another column SQL

Question

Overall question:

I have a table in my SQL Server with 3 columns (ID, Value, Decimal). So what I want to do in theory is SELECT the column Value and it should be formatted to the number of decimals specified in that same row.

What I've tried:

SELECT CONVERT(Decimal(18,s.Decimal), s.Value ) FROM Table s

and similar queries with CAST and FORMAT

What I want:

Table:

ID  Value        Decimal
1   500.754254   2

When I run the query it must return Value as '500.75'

Extra info: I am displaying the result in a WPF (C#) datagrid, and if it will be easier to format there, I don't mind. I just don't know how to.

In a comment below you state: I am working with medical risk categories and every category has got a different number of decimals. If I get this correctly you should not use a numeric type at all. What happens if your value is 500.756 and you get it rounded as 500.76? I suggest to use a string type and store the part before the dot and the part behind in two different columns. Combine them only for your output... — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 9, 2018 at 8:44
That is a very valid point. For all calculations we are using the full value, in the table it is a [Decimal(18,6)] so what happened was it was displaying for some columns 123.120000 instead of just the required amount of decimals which is also specified. — Hancs
– Hancs, Commented Oct 9, 2018 at 8:57
@Hancs you mean decimal(18, 6)? Since it is for display purposes you could use the format function in SQL server or format it in the application. — Salman Arshad
– Salman Arshad, Commented Oct 9, 2018 at 9:10

Zohar Peled · Accepted Answer · 2018-10-09 07:52:36Z

This is a presentation issue, and as such, should be handled by the presentation layer. c# have very simple ways to format the output, but as you wrote in your question, you would like to know how to do it in T-SQL - so here is one option, using cast, charindex and left.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    id int identity(1,1),
    [Value] decimal (18,10),
    [Decimal] int
)

INSERT INTO @T ([Value], [Decimal]) VALUES
(500.754254, 2),
(50.75636, 3),
(12345.7254, 1),
(1.424, 4)

The query:

SELECT  ID, 
        Value, 
        [Decimal],
        LEFT(CAST([Value] As varchar(18)), CHARINDEX('.', CAST([Value] As varchar(18))) + [Decimal]) As [Left] 
FROM @T

Results:

ID  Value               Decimal Left
1   500,7542540000      2       500.75
2   50,7563600000       3       50.756
3   12345,7254000000    1       12345.7
4   1,4240000000        4       1.4240

Tim Biegeleisen · Accepted Answer · 2018-10-09 07:44:36Z

1

We can try using FORMAT here:

SELECT FORMAT(123.123456, '##.' + LEFT('##########', Decimal))
FROM yourTable;

Demo

The trick above is that the width of the decimal component of the mask is determined by the value of the Decimal column. If you expect to need more than 10 decimal places, then just widen the ### string literal.

Another option might be using ROUND:

SELECT ROUND(Value, Decimal)
FROM yourTable;

However, this sort of formatting is usually best handled in your presentation layer. So, I would probably be looking to WPF/C# for a solution.

edited Oct 9, 2018 at 7:44

answered Oct 9, 2018 at 7:06

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

9 Comments

Hancs Over a year ago

Thanks for helping, it is not returning the exact answer expected but far better than I have gotten link (see the image of result set)

Hancs Over a year ago

Maybe I should just explain why it should show the exact decimal, I am working with medical risk categories and every category has got a different number of decimals

Tim Biegeleisen Over a year ago

If you can show me what is wrong with my answer, I'm happy to edit. Please do not paste a bunch of data here as comment. Instead, maybe setup a demo and paste the link.

Hancs Over a year ago

Check the link in my first comment. That is the result set from your query

Tim Biegeleisen Over a year ago

@Hancs Your image link is not readable at all, and I don't understand what those numbers. Check this demo to see that my first option with FORMAT is working.

|

Collectives™ on Stack Overflow

Format the number of decimals based on another column SQL

2 Answers 2

Comments

Demo

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related