CODEWITHSUNDEEP
sqlt-sql
lije
lije

Reputation: 420

Removing leading and trailing zeros and have the result in two decimal places

I wanted to remove both leading and trailing zeros, and want to have the result in two decimal places. For example: if the number is 0.01, I want to see .01; if the number is 0.010, I want to see .01; if the number is 0.10, I want to see .10; if the number is 1, I want to see 1.00.

I would use a FORMAT function. For example: 

SELECT FORMAT (0.090, '#.######')

works fine that it displays .09

However, when I use the same statement for a value greater than 1, it doesn't show the decimal places.

For example: 

SELECT FORMAT (10, '#.######')

gives 10 while I'm expecting 10.00

I tried to use a CASE statement to solve this in vain. A case statement works fine for a value more than 1, but it doesn't remove the leading zeros. 

DECLARE @a float = 10
SELECT
CASE 
    WHEN @a <1 THEN  format (@a, '#.######') 
    ELSE CAST(FORMAT(@a, '#.######') AS money) 
END

gives 10.00 as I expect. However, when the value of @a is 0.090 for example, it gives 0.09 while I expect .09

Is there any way I can remove both trailing and leading zeros and have the result in two decimal places? Any guide is much appreciated.

Upvotes: 2

Views: 178

Answers (1)

Nicola Lepetit
Nicola Lepetit

Reputation: 793

While I agree with D Stanley that 'Formatting is the responsibility of the display layer', I know sometimes you need some dirty functions to be used inside stored procedures or other functions. Try with this one:

CREATE FUNCTION dbo.toStr(@a float)  
RETURNS varchar(12)
AS 
BEGIN  
  DECLARE @ret varchar(12)
  SELECT @ret =
   CASE 
    WHEN @a < 1 THEN replace(format(@a, '.##'), '0.','.')
    ELSE FORMAT(@a, '#.00')
   END 
  RETURN @ret 
END;

I have tested with the following values:

select dbo.toStr(0.090);  -- .09
select dbo.toStr(1.09);   -- 1.09
select dbo.toStr(10.00);  -- 10.00
select dbo.toStr(1.2);    -- 1.20

Upvotes: 1

Related Questions