cancel
Showing results for 
Search instead for 
Did you mean: 

How do I format numbers in SQLA

reimer_pods
Participant
5,612

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 ;-).

VolkerBarth
Contributor

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?

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

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);
*/
VolkerBarth
Contributor
0 Kudos

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

reimer_pods
Participant
0 Kudos

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.

Answers (0)