/*
Convert numbers to uppercase
20081218 Clinton
*/
Create or replace function f_num_upper (SZ number)
Return varchar2 is
Szdx varchar2 (100 );
Lszf varchar2 (100 );
ZF varchar2 (100 );
Qsz varchar2 (1 );
I number;
J number;
Z number;
BJ number;
ZZ varchar2 (30 );
Begin
Szdx: = '';
Lszf: = '';
BJ: = 0;
ZZ: = '';
-------------
Z: = trunc (SZ/100000000 );
If z <> 0 then
BJ: = 1;
I: = length (TRIM (to_char (trunc (z ))));
J: = 1;
Qsz: = '';
Loop
If qsz = '0' and substr (TRIM (to_char (trunc (z), J, 1) = '0' then
ZF: = '';
Else
If (I = 1 and substr (TRIM (to_char (trunc (z), J, 1) = '0') then
ZF: = '';
Else
ZF: = substr ('0, 1, 3, 3}), to_number (substr (TRIM (to_char (trunc (z), J, 1) + 1, 1 );
End if;
End if;
Lszf: = ZF;
If substr (TRIM (to_char (sz), J, 1) <> '0' then
Lszf: = ZF | trim (substr ('pick up two progress', I * 2 ));
End if;
Qsz: = substr (TRIM (to_char (trunc (z), J, 1 );
Szdx: = szdx | lszf;
I: = I-1;
J: = J + 1;
Exit when j> length (TRIM (to_char (trunc (z ))));
End loop;
If substr (TRIM (szdx), length (TRIM (szdx), 1) = '0' then
Szdx: = substr (TRIM (szdx), 1, length (TRIM (szdx)-1 );
End if;
Szdx: = szdx | '100 ';
End if;
-------------
-------------
Z: = trunc (SZ-trunc (SZ/100000000) * 100000000)/10000 );
If bj = 1 then
ZZ: = lpad (TRIM (to_char (trunc (z), 4, '0 ');
Else
ZZ: = trim (to_char (trunc (z )));
End if;
If z <> 0 or bj = 1 then
BJ: = 1;
I: = length (zz );
J: = 1;
Qsz: = '';
Loop
If qsz = '0' and substr (ZZ, J, 1) = '0' then
ZF: = '';
Else
If (I = 1 and substr (ZZ, J, 1) = '0') then
ZF: = '';
Else
ZF: = substr ('0, 1, 3, 3), to_number (substr (ZZ, J, 1) + 1, 1 );
End if;
End if;
Lszf: = ZF;
If substr (ZZ, J, 1) <> '0' then
Lszf: = ZF | trim (substr ('pick up two progress', I * 2 ));
End if;
Qsz: = substr (ZZ, J, 1 );
Szdx: = szdx | lszf;
I: = I-1;
J: = J + 1;
Exit when j> length (zz );
End loop;
If substr (TRIM (szdx), length (TRIM (szdx), 1) = '0' then
Szdx: = substr (TRIM (szdx), 1, length (TRIM (szdx)-1 );
End if;
Szdx: = szdx | 'wan ';
If substr (TRIM (szdx), length (TRIM (szdx)-1, 1) = 'yie' then
Szdx: = substr (TRIM (szdx), 1, length (TRIM (szdx)-2) | 'yi0 ';
End if;
End if;
-------------
-------------
Z: = SZ-trunc (SZ/10000) * 10000;
If bj = 1 then
ZZ: = lpad (TRIM (to_char (trunc (z), 4, '0 ');
Else
ZZ: = trim (to_char (trunc (z )));
End if;
If z <> 0 or bj = 1 then
I: = length (zz );
J: = 1;
Qsz: = '';
Loop
If qsz = '0' and substr (ZZ, J, 1) = '0' then
ZF: = '';
Else
If (I = 1 and substr (ZZ, J, 1) = '0') then
ZF: = '';
Else
ZF: = substr ('0, 1, 3, 3), to_number (substr (ZZ, J, 1) + 1, 1 );
End if;
End if;
Lszf: = ZF;
If substr (ZZ, J, 1) <> '0' then
Lszf: = ZF | trim (substr ('pick up two progress', I * 2 ));
End if;
Qsz: = substr (ZZ, J, 1 );
Szdx: = szdx | lszf;
I: = I-1;
J: = J + 1;
Exit when j> length (zz );
End loop;
End if;
-------------
If substr (TRIM (szdx), length (TRIM (szdx)-1, 2) = '0' then
Szdx: = substr (TRIM (szdx), 1, length (TRIM (szdx)-2 );
End if;
If substr (TRIM (szdx), length (TRIM (szdx), 1) = '0' then
Szdx: = substr (TRIM (szdx), 1, length (TRIM (szdx)-1 );
End if;
Return szdx;
End;