UserDefined Function to Trim Zeros from decimal value

Most of the times for reporting purpose we want dont want to show extra zeros that comes along with decimal values. To my surprise SQL Server doesnt provide any function which will help trimming extra useless zeros from decimal. After searching a lot about this I found that if you want you have to write your own function which will serve this purpose for you.
And I found one scalar function which you can call in your procedures to trim extra zeros from a decimal value. Pass the decimal value, variable or column you want to remove the extra zeros from to the function, and it returns a trimmed string:


CREATE FUNCTION [dbo].[fnTrimZeros]
(
@decValue decimal(7,5)
)
RETURNS varchar(9)
AS
BEGIN
DECLARE @txtTrimmed varchar(9), @chrTest varchar(1)

SELECT @txtTrimmed = Convert(varchar(8),@decValue)
SELECT @chrTest = Substring(@txtTrimmed,Len(@txtTrimmed),1)
WHILE @chrTest = '0'
BEGIN
SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))
SELECT @chrTest = substring(@txtTrimmed,len(@txtTrimmed),1)
END
IF @chrTest = '.' -- remove unnecessary decimal point
SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))

RETURN @txtTrimmed + '%' -- optional % sign formatting

END



Call the function by:

SELECT dbo.fnTrimZeros(@DecimalVariable) AS TrimmedDecimal

-or-

SELECT dbo.fnTrimZeros(DecimalColumn) AS TrimmedDecimal FROM dbo.DecimalTable

You can adjust the precision of the decimals, or length of the varchars to suit your needs. Also, if you convert the trimmed string back to a decimal, you're probably just going to add extra zeros back on since you can't predict the precision of the decimal on the fly...
Ideally these kind of string manipulation should be done on the presentation layer (when possible, 'cause sometimes it's not!) instead of doing this in back end.