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)
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.

t-sql-function-remove-trailing-zeros-in-tsql






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