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: 12%

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 http://sqlanywhere-forum.sap.com/questions/335 and http://sqlanywhere-forum.sap.com/questions/394 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](http://url.com/ "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,233 times

last updated: 10 Dec '10, 17:33