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, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, 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.


Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

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