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

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









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