В теме одно сообщение

[LKhiger]

Чтобы улучшить чтение (особо важно для больших чисел) делим целую часть числа на тройки, начиная от десятичной точки, разделяя тройки цифр запятыми.

Для этого я написал монопрограмму, которую вы можете использовать в своей повседневной практике:

with input (number) as
(select -123456789.012 from sysibm.sysdummy1
 union all 
 select -6789.012256 from sysibm.sysdummy1
 union all 
 select 1000 from sysibm.sysdummy1
 union all 
 select 126789.01 from sysibm.sysdummy1
 ) 
,
transform_1 (num, part, rem, trans, fract, k, dum, Mdum ) as
(select min(number), int(0), int(abs(min(number))), varchar('', 150), 
		abs(min(number)) - int(abs(min(number))), int(12), int(1), 5 * count(*) 
  from input  

union all

Select 
num, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
		   when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
				 case when num < 0 then '-' else '' end 
			  || strip(digits(int(rem / power(10, k - 3))), l, '0') 
			else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
	   end 
	   || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), b, '0') else '' end, l, '0')
, fract, k - 3, dum + 1, Mdum
  from transform_1
where k - 3 >= 0  
  and dum + 1 <= Mdum

union all

Select mnum, 0, anum, '', frct, 12, dum + 1, Mdum 
  from transform_1, table 
(select min(number) mnum, 0, abs(min(number)) anum, abs(min(number)) - int(abs(min(number))) frct
  from Input  
  where number > num ) ii 
where k - 3 < 0  
  and mnum is not null
  and dum + 1 <= Mdum
)
select varchar(num) "Decimal.................", 
		 trans "Formatted"
  from transform_1 tr where k = 0

Result:

Decimal................. .............Formatted
-123456789.012........... -123,456,789.012
-6789.012256.........................-6,789.012256
1000.......................................1,000
126789.01........................... 126,789.01

Lenny Khiger, ADSPA&VP

[LKhiger]

Если вы хотите создать собственную функцию вам не нужно обрабатывать множественный входящий поток и вы можете исользовать облегчённый код:

with input (number) as
(select -123456789.012345 from sysibm.sysdummy1
) 
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(abs(number)), varchar('', 150), 
		abs(number) - int(abs(number)), 12 
  from input where abs(number) >= 1000   
union all
select 
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
		   when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
				 case when num < 0 then '-' else '' end 
			  || strip(digits(int(rem / power(10, k - 3))), L, '0') 
			else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
	   end 
	   || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), b, '0') else '' end, L, '0')
, fract, k - 3
  from transform_1, input  
where k - 3 >= 0 and abs(number) >= 1000  
union all
select number, int(0), int(0), varchar(number), number, 0  
from input
where abs(number) < 1000  
)
select num "Input.................", 
		 trans "Result"
  from transform_1 tr where k = 0

Input................. .................................Result
-123456789.012345.......................-123,456,789.012345

Lenny