[lennykhiger]

I'd like to format the decimal number 126789.01 to 126,789.01.

The following query can solve our problem:

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) "Input.................", 
         trans "Result"
  from transform_1 tr where k = 0

Result:

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

Lenny Khiger, ADSPA&VP

[lennykhiger]

Common to this problem is the problem of selecting rows with only numeric data in some column of the table, which consists the mixed data.

For example in table1 we have column1 Varchar(100):

Column1

'abc122'
'cdef6789'
'123654'
' 234 456'
'345.67'
'345...67'
'-787.001'
'567+675.44'
'777.888-'
'+987234570911'

So, we have to create SQL query, which will select

Column1

'123654'
'345.67'
'-787.001'
'777.888-'
'+987234570911'

only....

How we can understand we have to select string with
'0123456789+-.', also in string could one and only one '-', '+' and '.'.

In practice it is not so seldom problem, how I know.

Algorithm:

step1: Remove space from the string. String has not to be change
step2: Transate all characters in string '+-.0123456789' to space.
step3: Remove '-', '+', '.' from the string. For each length of the string after character removed has to become 1 character less
step4. Find position of '+' and '-'. Possition has to be first or last in the string
step5. '+' and '-' has not to be in the same string same time

select column1 
from 
(select   case when substr(strip(column1), length(strip( column1)), 1) 
                                        in ('+', '-') 
                    then 
                    substr(strip(column1), length(strip( column1)), 1)  
                    || 
                    substr(strip(column1), 1, length(strip(column1)) - 1) 
                    when substr(strip( column1), 1, 1) in ('+', '-') 
                    then 
                    substr(strip(column1), 1, 1) 
                    ||  
                    strip(substr(strip(column1), 2)) 
                    else    strip(column1)  
                    end AS column1 
   from table1 
   where Translate(column1, ' ', '0123456789+-.') = ' ' 
     and  column1 > Translate(column1, ' ', '0123456789') 
) ii 
where 
replace( column1, ' ', '') = column1  
and 
length(replace( column1, '+', '')) >= length(column1) - 1 
and 
length(replace( column1, '-', '')) >= length(column1) - 1 
and 
length(replace( column1, '.', '')) >= length(column1) - 1 
and 
posstr(column1, '-')  in (0, 1) 
and 
posstr(column1, '+')  in (0, 1) 
and not exists 
(select * from sysibm.sysdummy1 
  where 
posstr(column1, '-') > 0 
and 
posstr(column1, '+') > 0)

Lenny Khiger, ADSPA&VP