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)
    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



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:

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