SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.

download SQL Server 2016
download SQL Server 2014

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)

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

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

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

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

SELECT @strValue = @strValueL +
 CASE WHEN (LEN(@strValueR) > 0) THEN
  '.' + @strValueR

RETURN @strValue

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.


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.


Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2015 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems