T-SQL Function to Remove Trailing Zeros in SQL Server
Here is the t-sql code of a tsql function which you can use to remove trailing zeros.
Removing sql trailing zeros can be easily managed by using tsql the user defined function udf_RemoveTrailingZeros.
ALTER FUNCTION udf_RemoveTrailingZeros
(
@strValue nvarchar(50)
)
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 = @strValueL +
CASE WHEN (LEN(@strValueR) > 0) THEN
'.' + @strValueR
ELSE
''
END
RETURN @strValue
END
You can use the above t-sql on your SQL Server to remove trailing zeros.
Before using the t-sql function udf_RemoveTrailingZeros, check the following example output with different trailing zero value parameters.
SELECT
dbo.udf_RemoveTrailingZeros('1000.0'),
dbo.udf_RemoveTrailingZeros('1000.000'),
dbo.udf_RemoveTrailingZeros('1000.010'),
dbo.udf_RemoveTrailingZeros('1000.'),
dbo.udf_RemoveTrailingZeros('1001.0'),
dbo.udf_RemoveTrailingZeros('1000')
And below is the output of the above t-sql select statement which is calling the user defined function udf_RemoveTrailingZeros for removing the trailing zeros using t-sql.
