CODEWITHSUNDEEP
sql-servert-sql
sgsergio
sgsergio

Reputation: 99

Derive number till 2 decimal places from a decimal or float value

There is a requirement in which there is a column (decimal/float) having values ranging from integers (like 1) to numbers having upto 6 decimal places (like 1.123456). I want to get the values as explained in logic below.

 value1     |   value_new
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
13.123456   |   131234.56   --logic: value1: if there are 6 values after decimal, value_new: multiply value1 by 10000
0.12345     |   123.45      --logic: value1: if there are 5 values after decimal, value_new: multiply value1 by 1000
13.1234     |   1312.34     --logic: value1: if there are 4 values after decimal, value_new: multiply value1 by 100 
13.123      |   131.23      --logic: value1: if there are 3 values after decimal, value_new: multiply value1 by 10 
13.12       |   13.12
13.1        |   13.1
13          |   13
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Create table:

DECLARE @T AS TABLE
(
    Value1 float
)

INSERT INTO @T (Value1) VALUES
(13.123456),
(0.12345),
(13.1234),
(13.123),
(13.12),
(13.1),
(13);

Upvotes: 0

Views: 99

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82484

Update

One way to get there is by using cross apply to calculate an int that's 1000000 bigger than the original float value, and then % to determine how may digits after the decimal separator there are in the original number - and a simple case expression:

SELECT  Value1, 
        CASE 
            WHEN Base % 10000 = 0 THEN Value1 
            WHEN Base % 1000 = 0 THEN Value1 * 10
            WHEN Base % 100 = 0 THEN Value1 * 100
            WHEN Base % 10 = 0 THEN Value1 * 1000
            ELSE Value1 * 10000
        END As NewValue
FROM @T
CROSS APPLY (
    SELECT CAST((Value1 * 1000000) AS INT) As Base
) baseValue

Results:

Value1      NewValue
13,123456   131234,56
13,12345    13123,45
13,1234     1312,34
13,123      131,23
13,12       13,12
13,1        13,1
13          13

First version (The question is now different)

Though I agree this is the presentation layer responsibility,
Another option is to multiply by 100, cast to int, cast back to float and divide by 100 again.

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

DECLARE @T AS TABLE
(
    Value1 float
)

INSERT INTO @T (Value1) VALUES
(13.123456),
(13.12345),
(13.1234),
(13.123),
(13.12),
(13.1),
(13);

The query:

SELECT Value1, CAST(CAST(Value1 * 100 AS int) AS float) / 100 As NewValue
FROM @T

Results:

Value1      NewValue
13,123456   13,12
13,12345    13,12
13,1234     13,12
13,123      13,12
13,12       13,12
13,1        13,1
13          13

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

This REALLY belongs in the presentation layer, however, one option is to create string via format().

Please Note: format() is not very performant. It should be used sparingly.

Example

Select *
      ,NewValue = format(value1,'0.##')
 From  YourTable


value1      NewValue
13.123456   13.12
13.12345    13.12
13.1234     13.12
13.123      13.12
13.12       13.12
13.1        13.1
13          13

Upvotes: 1

Related Questions