Friday, May 9, 2008

How to strip trailing zeros

A client came to me with another string formatting problem. I would never recommend formatting strings in T-SQL, but rather to do formatting on the client side. The question was to strip trailing zeros from a numeric value stored in a string but retain the decimals when they are not zero.

   
1.0501needs to show1.0501
1.0500needs to show1.05
1.0000needs to show1
This is easy to do by replacing all zeroes with a space and then using RTRIM() to remove the right spaces. Finally replace the spaces again with zeroes and you have the correct result. Except when all decimals were zero, in that case the decimal point needs to be suppressed as well. I poured the code in a function that is easier to use.
CREATE FUNCTION dbo.fn_strip_zeros(@number numeric(38,10))
RETURNS varchar(38)
WITH ENCRYPTION
AS
  BEGIN
    DECLARE @result varchar(38)
    DECLARE @decimal varchar(3)

    SET @decimal = substring(convert(varchar(38), convert(numeric(38,10),1)/5 ), 2,1)

    set @result = rtrim(replace(replace(rtrim(replace(@number,'0',' ')),' ','0') + ' ', @decimal + ' ', ''))

    RETURN @result
  END

GO

4 comments:

brass said...

What is the purpose of this line?

SET @decimal = substring(convert(varchar(38), convert(numeric(38,10),1)/5 ), 2,1)

I have played around without that line and just using '.' instead of @decimal in the following line without any problems.

Another thread dealing with the same issue:
http://www.sqlservercentral.com/Forums/Topic286695-8-1.aspx#bm286736

Filip said...

Depending on the regional settings the decimal separator is a . or a ,

That line of code detects the separator.

brass said...

ahh, thanks. I knew there was a reason, I just couldn't figure it out.

Prasad said...

This has helped me to quickly fix one issue, thanks a lot for posting this