The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
4.1k324278
accept rate: 12%

edited 10 Dec '10, 17:33

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645

1

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)
deterministic 
begin 
   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; 
end;

-- Test cases
/*
select
   fnFormatNumber(1234.56),
   fnFormatNumber(1234.00),
   fnFormatNumber(12345678901234567890.1234567890),
   fnFormatNumber(0.00),
   fnFormatNumber(0),
   fnFormatNumber(-.07),
   fnFormatNumber(-1234.5678),
   fnFormatNumber('abcdef'),
   fnFormatNumber(null);

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

select
   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
29.3k287438645
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

By RSS:

Answers

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:

×106
×10
×9
×5

question asked: 09 Dec '10, 12:57

question was seen: 2,182 times

last updated: 10 Dec '10, 17:33