CODEWITHSUNDEEP
sqlsql-serversql-server-2012
AllSolutions
AllSolutions

Reputation: 1266

SQL How to convert number to text with minimum decimal places (dynamic number of decimal places) in SQL

In an SQL query, I want to convert numeric value to text with minimum no. of decimal places, example if the number is 2.50, then I want output as 2.5; if number is 3, then I want output as 3; if number is 18.75, I want output as 18.75, etc.

How can I achieve this?

EDIT 1: To give more background, I am dividing 2 numeric values, and want the result in text with minimum required decimal places.

Thanks.

Upvotes: 2

Views: 4690

Answers (3)

Jason A. Long
Jason A. Long

Reputation: 4442

This should do the trick... 

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    SomeNumber DECIMAL(9,7) NOT NULL 
    );
INSERT #TestData (SomeNumber) VALUES 
    (1.2345670), (1), (99.00100), (5.55);


SELECT 
    td.SomeNumber,
    REVERSE(STUFF(rcv.RevCastVarchar, 1, PATINDEX('%[^0.]%', rcv.RevCastVarchar) - 1, ''))
FROM
    #TestData td
    CROSS APPLY ( VALUES (REVERSE(CAST(td.SomeNumber AS VARCHAR(10)))) ) rcv (RevCastVarchar);

HTH, Jason

Upvotes: 0

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

In SQL 2012 and above you can write

SELECT FORMAT(15.0/4.0 , '#.########' )

It uses FORMAT function which uses .NET String.Format functionality.

Upvotes: 5

Ven
Ven

Reputation: 2014

If want to get 2 decimals for division values, use this 

 Select  case when right(cast ( x/y as decimal(18,2)),1) = 0 
              then left (cast ( x/y as decimal(18,2)),3) 
              else cast ( x/y as decimal(18,2)) end ReqOutput

Upvotes: 0

Related Questions