Friday, May 9, 2008

How to strip trailing zeros

A client came to me with another string formatting problem. I would never recommend formatting strings in T-SQL, but rather to do formatting on the client side. The question was to strip trailing zeros from a numeric value stored in a string but retain the decimals when they are not zero.

   
1.0501needs to show1.0501
1.0500needs to show1.05
1.0000needs to show1
This is easy to do by replacing all zeroes with a space and then using RTRIM() to remove the right spaces. Finally replace the spaces again with zeroes and you have the correct result. Except when all decimals were zero, in that case the decimal point needs to be suppressed as well. I poured the code in a function that is easier to use.
CREATE FUNCTION dbo.fn_strip_zeros(@number numeric(38,10))
RETURNS varchar(38)
WITH ENCRYPTION
AS
  BEGIN
    DECLARE @result varchar(38)
    DECLARE @decimal varchar(3)

    SET @decimal = substring(convert(varchar(38), convert(numeric(38,10),1)/5 ), 2,1)

    set @result = rtrim(replace(replace(rtrim(replace(@number,'0',' ')),' ','0') + ' ', @decimal + ' ', ''))

    RETURN @result
  END

GO

 
{"cc_re__cc_re__cc_by_3_0": [" type='application/atom+xml'>Posts (Atom)</a>\n</div>\n</div>\n<script type='text/javascript'>\n    window.___gcfg = { 'lang': 'en' };\n  </script>\n</div></div>\n</div>\n<div id='sidebar-wrapper'>\n<div class='sidebar section' id='sidebar'><div class='widget Text' data-version='1' id='Text1'>\n<h2 class='title'>It's obvious but...</h2>\n<div class='widget-content'>\nDon't run code you download from the internet on a production system before testing it properly.<br/><br/>All content is protected by the<br/><a href=\"http://creativecommons.org/licenses/by/3.0/\">CCA 3.0</a> license.<br/>\n</div>\n<div class='clear'></div>\n<span class='widget-item-control'>\n<span class='item-control blog-admin'>\n<a class='quickedit' href='//www.blogger.com/rearrange?blogID=6516081340051915164&widgetType=Text&widgetId=Text1&action=editWidget&sectionId=sidebar' onclick='return _WidgetManager._PopupConfig(document.getElementById(\"Text1\"));' target='configText1' title='Edit'>\n<img alt='' height='18' src='https://resources.blogblog.com/img/icon18_wrench_allbkg.png' width='18'/>"]}