The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

I'd like to have something like NUMBERFORMAT analogous to DATEFORMAT, to format a numeric value into a string. Would take a numeric value (integer, double, numeric...) and a format expression (e.g. '#,###.00' as in PowerBuilder).

Already there? Then tell me where, please ;-).

asked 09 Dec '10, 12:57

Reimer%20Pods's gravatar image

Reimer Pods
accept rate: 11%

edited 10 Dec '10, 17:33

Volker%20Barth's gravatar image

Volker Barth


Unfortunately not there, AFAIK (but possibly planned for future versions)... May the questions and help?

(09 Dec '10, 13:50) Volker Barth

Just a function you might like to use. It's not exactly what you are looking for (and doesn't add leading zeroes) but could help as a starting point.

I use this primarily for converting numbers into German convention (i.e. decimal comma, point as thousand separator) when there's no client-side code to do so, e.g. when unloading data into text files used for Word imports.

Note: This sample is published as-is - use with care:)

-- Formatting numbers in German convention
-- Turns decimal point into decimal comma, sets thousands separator
-- and returns numbers with the according count of decimal places.
-- If the input value is not a valid number, NULL is returned.

create function dbo.fnFormatNumber(strValue varchar(100), nCntDecimalPlaces int default 2)
returns varchar(200)
   declare strReturn varchar(200); 
   declare i integer; 
   declare j integer;

-- Return NULL for non-numeric values
   if isnumeric(strValue) = 0 then
      return null;
   end if;

-- Round/Extend to the given number of decimal places and trim all whitespace
   if nCntDecimalPlaces < 0 then
      set nCntDecimalPlaces = 0;
   end if;

set strValue = str(strValue, 100, nCntDecimalPlaces);
   set strValue = trim(strValue);
   set i = length(strValue); 
   set j = 0;

-- Insert thousand separators
   while i > 0 loop 
      set strReturn = substr(strValue, i, 1) || strReturn; 
      if (locate(strReturn, '.') > 0) or (locate(strValue, '.') = 0) then 
         if substr(strValue, i, 1) <> '.' then 
            set j = j + 1;
         end if; 
      end if; 
      if (j = 3) and (i > 1) then 
         set strReturn = ',' || strReturn; 
         set j = 0;
      end if; 
   set i = i - 1; 
   end loop;

-- Switch point and comma
   set strReturn = replace(strReturn, '.', '|');
   set strReturn = replace(strReturn, ',', '.');
   set strReturn = replace(strReturn, '|', ',');

return strReturn; 

-- Test cases

   fnFormatNumber(1234.56789, 2),
   fnFormatNumber(1234.56789, 5),
   fnFormatNumber(1234.56789, 0),
   fnFormatNumber(1234.56789, -1),
   fnFormatNumber(1234.56789, 1),
   fnFormatNumber(1234.56789, 3);

   fnFormatNumber(1234, 2),
   fnFormatNumber(1234, 5),
   fnFormatNumber(1234, 0),
   fnFormatNumber(1234, -1),
   fnFormatNumber(1234, 1),
   fnFormatNumber(1234, 3);
permanent link

answered 10 Dec '10, 17:33

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

A further note: This code may be based on contributions from the SQL Anywhere NNTP groups years ago. I'm not sure as I would usually have written down an according note, and here I haven't...

(10 Dec '10, 17:49) Volker Barth

Thanx, Volker! I'd still like to be able to specify a format string. But I could use this function in some cases to replace a similar function we've written that doesn't insert thousand separators.

(13 Dec '10, 11:51) Reimer Pods
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 09 Dec '10, 12:57

question was seen: 2,391 times

last updated: 10 Dec '10, 17:33