CODEWITHSUNDEEP
sqlsql-servert-sql
Thej
Thej

Reputation: 375

Minimize the length of number to fixed length

I need to minimize the length of number to fixed value 3 with decimal point 1

Ex: After calculation value of @a=45689.45.

Now I need to get value of @a =45.6 If the value is less then 100 i.e if it is 89.63 then I don't need to change it.

At last value of @a should be decimal of (3,1)

Upvotes: 1

Views: 81

Answers (3)

Alex K.
Alex K.

Reputation: 175876

How about:

case when @a >= 100 then
    round(@a / power(10, floor(log10(@a)) - 1), 1, 1)
else @a
end

Upvotes: 1

Rhys Jones
Rhys Jones

Reputation: 5518

I haven't tested this to death but without using string manipulation this might do the job;

declare @i decimal(18, 6); set @i = 2000

select cast(round(@i / case when @i >= 100 then (power(10, floor(log10(@i)) - 1)) else 1.0 end, 1) as decimal(3,1)

Fingers crossed :)

Rhys

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Try this

DECLARE @a DECIMAL(7, 2)=45689.45

SELECT CASE
         WHEN len(CONVERT(INT, @a)) > 3 THEN LEFT(@a / CONVERT(INT, '1' + replicate(0, len(CONVERT(INT, @a))-2)), 4)
         ELSE @a
       END

Upvotes: 0

Related Questions