on 2010 Dec 09 12:57 PM
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 ;-).
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);
*/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.