Share this post

Remove Trailing Zeros

CREATE FUNCTION [dbo].[udf_RemoveTrailingZeros]
(
	@strValue nvarchar(50),
	@KeepComma bit = 0,
	@RemoveBefore bit = 0
)
RETURNS nvarchar(50)
BEGIN

DECLARE @strValueR nvarchar(50)
DECLARE @strValueL nvarchar(50)
DECLARE @charInd int
DECLARE @intCount int

SET @charInd = CHARINDEX('.',@strValue)

IF @charInd = 0
 SET @strValueL = @strValue
ELSE
BEGIN
 SET @strValueR = RIGHT(@strValue, LEN(@strValue)-@charInd)
 SET @strValueL = LEFT(@strValue, @charInd-1)

 SET @intCount = LEN(@strValueR)+1
 WHILE @intCount > 0
 BEGIN
  SET @intCount = @intCount - 1
  IF SUBSTRING(@strValueR, @intCount, 1) NOT LIKE '0'
   BREAK
 END
 SELECT @strValueR = LEFT(@strValueR, @intCount)
END

SELECT @strValue = CASE WHEN @RemoveBefore = 1 THEN Cast(Cast(@strValueL as int) as varchar) ELSE @strValueL END +
 CASE WHEN (LEN(@strValueR) > 0) THEN
  '.' + @strValueR
 ELSE
  ''
 END

 --Replace point decimal by comma
 set @strValue = replace(@strValue,'.',',')

 IF @KeepComma = 1 AND SUBSTRING(@strValue, len(@strValue),1) <> ',' AND CHARINDEX(',',@strValue) <= 0
	SET @strValue = @strValue + ','

RETURN @strValue
END