Amount uppercase to lowercase SQL

Create Or Replace Function Money2chinese (money on number) Return Varchar2 is

Stryuan Varchar2 (150);

Stryuanfen Varchar2 (152);

Numlenyuan number;

Numlenyuanfen number;

Strrstyuan VARCHAR2 (600);

Strrstfen VARCHAR2 (200);

Strrst VARCHAR2 (800);

Type typetabmapping is Table of Varchar2 (2) Index by Binary_integer;

Tabnummapping typetabmapping;

Tabunitmapping typetabmapping;

Numunitindex number;

I number;

J number;

Charcurrentnum Char (1);

Begin

If money was Null then

Return Null;

End If;

Stryuan: = To_char (Money);

If Stryuan = ' 0 ' Then

Numlenyuan: = 0;

Stryuanfen: = Lpad (To_char (Money * 100)), 2, ' 0 ');

Else

Numlenyuan: = Length (Stryuan);

Stryuanfen: = To_char (Floor (Money * 100));

End If;

If Stryuanfen = ' 0 ' Then

Numlenyuanfen: = 0;

Else

Numlenyuanfen: = Length (Stryuanfen);

End If;

If Numlenyuan = 0 Or numlenyuanfen = 0 Then

Strrst: = ' 0 round whole ';

Return Strrst;

End If;

Tabnummapping (0): = ' 0 ';

Tabnummapping (1): = ' one ';

Tabnummapping (2): = ' II ';

Tabnummapping (3): = ' three ';

Tabnummapping (4): = ' premises ';

Tabnummapping (5): = ' Wu ';

Tabnummapping (6): = ' Lu ';

Tabnummapping (7): = ' qi ';

Tabnummapping (8): = ' ba ';

Tabnummapping (9): = ' jiu ';

Tabunitmapping (-2): = ' min ';

Tabunitmapping (-1): = ' angle ';

Tabunitmapping (1): = ";

Tabunitmapping (2): = ' pick ';

Tabunitmapping (3): = ' Bai ';

Tabunitmapping (4): = ' thousand ';

Tabunitmapping (5): = ' million ';

Tabunitmapping (6): = ' pick ';

Tabunitmapping (7): = ' Bai ';

Tabunitmapping (8): = ' thousand ';

Tabunitmapping (9): = ' billion ';

For I in 1.. Numlenyuan Loop

J: = Numlenyuan-i + 1;

Numunitindex: = Mod (i, 8);

If numunitindex = 0 Then

Numunitindex: = 8;

End If;

If Numunitindex = 1 and i > 1 then

Strrstyuan: = tabunitmapping (9) | | Strrstyuan;

End If;

Charcurrentnum: = substr (Stryuan, J, 1);

If charcurrentnum <> 0 Then

Strrstyuan: = tabnummapping (charcurrentnum) | |

Tabunitmapping (numunitindex) | | Strrstyuan;

Else

If (i = 1 Or i = 5) Then

If substr (Stryuan, j-3, 4) <> ' 0000 ' Then

Strrstyuan: = tabunitmapping (numunitindex) | | Strrstyuan;

End If;

Else

If substr (Stryuan, J + 1, 1) <> ' 0 ' then

Strrstyuan: = tabnummapping (charcurrentnum) | | Strrstyuan;

End If;

End If;

End If;

End Loop;

For I In-2. -1 Loop

J: = Numlenyuan-i;

Charcurrentnum: = substr (Stryuanfen, J, 1);

If charcurrentnum <> ' 0 ' then

Strrstfen: = tabnummapping (charcurrentnum) | | Tabunitmapping (i) | |

Strrstfen;

End If;

End Loop;

If Strrstyuan is isn't Null then

Strrstyuan: = Strrstyuan | | ' Round ';

End If;

If Strrstfen is Null then

Strrstyuan: = Strrstyuan | | ' Whole ';

elsif Length (Strrstfen) = 2 and substr (Strrstfen, 2) = ' angle ' Then

Strrstfen: = Strrstfen | | ' Whole ';

End If;

Strrst: = Strrstyuan | | Strrstfen;

--strrst: = Replace (Strrst, ' billion zero ', ' billion ');

--strrst: = Replace (Strrst, ' universal Zero ', ' million ');

Return Strrst;

End Money2chinese;

(This article is reproduced, but has forgotten to reprint the source)

Amount uppercase to lowercase SQL