How to string a decimal and keep commas and dot

[Vlad]

Hi,

ASA 9.0.2.3221

This is probably very simple to do, but I'm really struggling to find an
answer in the docs:

I have a decimal (16,4), I need to string it to text, but it is losing
commas which denote thousands. It comes out as 100000.00, whereas I need to
have it as 100,000.00, but am getting 100000.00

I am casting it like:

string(cast("table"."price" as decimal(16,4))), and I get 100000.00

I could do this in Powerbuilder as a computed field, and "mask" the string
as string(price, "#,##0.00"), however, it is very important that I do it
within SQL query, and if I try

string(cast("table"."price" as decimal(16,4)), "#,##0.00") it thinks that
"#,##0.00" is a name of the column, and if I try
string(cast("table"."price" as decimal(16,4)), '#,##0.00'), then it just
prints #,##0.00 at the end of the price.

Is this possible to do in SQL?

Cheers

[Justin Willey]

As you say, its normally a job for the front end app esp if you need to take
account of local customs. If you need to do it in SQL, this function should
do the trick:

CREATE FUNCTION "pears"."CommaThousandsDec"
(in d double)
returns char(40)
begin
declare rv char(40);
declare s char(20);
declare t char(20);
declare i integer;
declare c char(1);
declare fract double;
set fract=abs(remainder(d,1));
if fract = 0 then set fract=null
end if;
if d is null then
return null
end if;
if d < 0 then
set d=abs(d);
set c='-'
else
set c=''
end if;
set rv='';
set s=trim(str(d,20,0));
set i=-1;
lbl: loop
set t=substr(s,i,-3);
if t = '' then
leave lbl
end if;
if rv <> '' then
set rv=','+rv
end if;
set rv=t+rv;
set i=i-3
end loop lbl;
return(c+rv+string(fract))
end;

Justin Willey

"Vlad" <vl...@NOSPAMpcr.ltd.uk> wrote in message
news:4799f8ee@forums-1-dub...

[Unknown]

The fact is, you are not losing anything the comma is
a formatting thing that PB gives you due to your format
string.

By SQL do you mean dbisql? If so, you may find
casting it as decimal works a little better [only in the
send of providing you get the millenial seperation you
are looking for] but that is not the same thing as
[regionally sensitive] output formatting.

In genernal the CAST and CONVERT functions
are meant to perfrom datatype conversions and
are not formatting operations (like [f]printf or
format statements in Fortran, say, or PB's
display mask are).

To do that in SQL one needs to codify that using
either
- modulus arithmatic to determine the millenial
values and then concatinating the strings together
from those pieces
or
- locating the decimal point's location and doing
substring and length operations relative to that;
again concatinating the desired format together
from those pieces.

Generalizing this into your own user defined SQL function
would make this a lot more user friendly, once you work
out the details.

Why so hard in SQL? It is not the function of SQL to
be regional format sensitive on datatypes. In fact it is
not necessarily known to the server which regional
format the client could desire [which can be different
for each and every client]. This job is left to the
cleint application code and the settings in the client
OS and the IDE and the choices of the programmer.

"Vlad" <vl...@NOSPAMpcr.ltd.uk> wrote in message
news:4799f8ee@forums-1-dub...

[Usmanov A.]

Or:

CREATE FUNCTION "DBA"."formatNumber"(@in double, @r integer default 0, @f
char default '.',@t char default '')
RETURNS long varchar
begin
declare @bb bigint;
declare @out long varchar;
declare @pattern long varchar;
set @pattern = '___'||@f||repeat('_',@r);
set @bb = 0.5+@in*POWER(10,@r);
set @out = cast(@bb as long varchar);
set @out = insertstr(length(@out)-@r,@out,@f);
set @bb = PATINDEX('%'||@pattern,@out);
while @bb>1 loop
set @out = insertstr(@bb-1,@out,@t);
set @pattern = '___'||@t||@pattern;
set @bb= PATINDEX('%'||@pattern,@out);
end loop;
return @out;
end

@r - A positive integer specifies the number of significant digits to the
right of the decimal point at which to round. A negative expression
specifies the number of significant digits to the left of the decimal point
at which to round.
@t - thousand's separator char
@f - decimal point char
I like puzzles :-)

--
Best regards,

Alex Usmanov
ASA\ML\UL 9.0.2.3361

"Justin Willey" <g...@nospamatall.iqx.co.uk> сообщил/сообщила в новостях
следующее: news:479a0475$1@forums-1-dub...

[Vlad]

Thanks guys

"Usmanov A." <ale...@pochtamt.ru> wrote in message
news:479df557@forums-1-dub...