How do I format numbers in SQLA

reimer_pods — Thu, 09 Dec 2010 12:57:03 GMT

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

Re: How do I format numbers in SQLA

VolkerBarth — Thu, 09 Dec 2010 13:50:36 GMT

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?

Re: How do I format numbers in SQLA

VolkerBarth — Fri, 10 Dec 2010 17:33:10 GMT

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); */

Re: How do I format numbers in SQLA

VolkerBarth — Fri, 10 Dec 2010 17:49:18 GMT

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

Re: How do I format numbers in SQLA

reimer_pods — Mon, 13 Dec 2010 11:51:20 GMT

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.

Question Re: How do I format numbers in SQLA in Technology Q&A

How do I format numbers in SQLA

Re: How do I format numbers in SQLA

Re: How do I format numbers in SQLA

Re: How do I format numbers in SQLA

Re: How do I format numbers in SQLA