Oracle numbers and Chinese character conversion

This is an oracle function that converts Arabic numerals and Chinese characters. For example, 1234567890.0123 can be converted to "1.2 billion, 34,567,890,", and can also be converted to corresponding numbers.

This function supports floating-point numbers within 1 GB, but does not support exponential form.
Add SQL code to favorites

/* Formatted on 2009/12/22 17:03 (Formatter Plus v4.8.8 )*/
Create or replace package czutil
AS
-- Converts numbers into Chinese characters and supports operations on 1-gigabit numbers
FUNCTION num2chi (numvalue in number)
RETURN VARCHAR2;

-- Converts Chinese characters to numbers and supports operations on 1-gigabit numbers
FUNCTION chi2num (chivalue IN VARCHAR2)
Return number;
END;

Create or replace package body czutil
AS
FUNCTION num2chi (numvalue in number)
RETURN VARCHAR2
IS
-- Ing table of Chinese Character ing numbers
Type map is table of VARCHAR2 (10)
Index by BINARY_INTEGER;

Ling_jiu_map MAP;
Shibaiqian_map MAP;
Wanyizhao_map MAP;
-- Temporary Variable
I INT: = 0; -- 'temporary variable
J INT: = 0; -- Temporary Variable
K INT: = 0; -- Temporary Variable
X INT: = 0; -- Temporary Variable
Y INT: = 0; -- Temporary Variable
Str VARCHAR2 (100): = ''; -- Temporary Variable
Tmp VARCHAR2 (100): = ''; -- Temporary Variable
Integer_part VARCHAR2 (100): = ''; -- integer part in the numerical form
Float_part VARCHAR2 (100): = ''; -- digit decimal part
Returnvalue VARCHAR2 (100): = ''; -- Return Value
BEGIN
-- Initialize an array
Ling_jiu_map (0): = '0 ';
Ling_jiu_map (1): = '1 ';
Ling_jiu_map (2): = '2 ';
Ling_jiu_map (3): = '3 ';
Ling_jiu_map (4): = '4 ';
Ling_jiu_map (5): = '5 ';
Ling_jiu_map (6): = '6 ';
Ling_jiu_map (7): = '7 ';
Ling_jiu_map (8): = '8 ';
Ling_jiu_map (9): = '9 ';
Shibaiqian_map (0): = '';
Shibaiqian_map (1): = '10 ';
Shibaiqian_map (2): = 'bai ';
Shibaiqian_map (3): = 'kil ';
Wanyizhao_map (0): = '';
Wanyizhao_map (1): = 'wan ';
Wanyizhao_map (2): = '661 ';
Wanyizhao_map (3): = 'mb ';
-- Split the number into two numeric arrays: integer and floating-point.
I: = INSTR (numvalue, '.', 1 );

IF I = 0
THEN
Integer_part: = SUBSTR (numvalue, 1 );
ELSE
Integer_part: = FLOOR (numvalue );
Float_part: = SUBSTR (numvalue, I + 1 );
End if;

-- Calculate the integer part
I: = LENGTH (integer_part );
J: = CEIL (I/4 );
K: = 0;

-- Divides each four integers into one group.
FOR k IN 0 .. j-1
LOOP
IF k = 0
THEN
Str: = SUBSTR (integer_part, 1, I-4 * j + 4 );
ELSE
Str: = SUBSTR (integer_part, I-4 * j + 4 * k + 1, 4 );
End if;

X: = LENGTH (str );
Y: = 0;
Tmp: = '';

-- Convert data in each group into Chinese Characters
FOR y IN 0.. x-1
LOOP
Tmp: =
Tmp
| Ling_jiu_map (SUBSTR (str, y + 1, 1 ))
| Shibaiqian_map (x-y-1 );
End loop;

-- Zero Processing
Tmp: = RTRIM (tmp, '0'); -- remove the zero in the first position
Tmp: = REGEXP_REPLACE (tmp, '(0 [])', '0'); -- zero on
Tmp: = REGEXP_REPLACE (tmp, '(0 {2,})', '0'); -- zero combination of thousands of digits
Returnvalue: = returnvalue | tmp | wanyizhao_map (j-k-1 );
End loop;

-- Zero Processing
-- Remove zero in
Returnvalue: = REGEXP_REPLACE (returnvalue, '(0 [10 million])', '0 ');
-- Zero integration of hundreds of millions of BITs
Returnvalue: = REGEXP_REPLACE (returnvalue, '(0 {2,})', '0 ');
-- One of the top 10
Returnvalue: = REGEXP_REPLACE (returnvalue, '^ 10', '10 ');
Returnvalue: = REGEXP_REPLACE (returnvalue, 'zero to 10', 'zero to 10 ');

-- Put the returned result
IF returnvalue IS NULL
THEN
Returnvalue: = '0 ';
End if;

IF float_part IS NOT NULL
THEN
-- Calculates the decimal part.
K: = 0;
Tmp: = '';

FOR k IN 1 .. LENGTH (float_part)
LOOP
Tmp: = tmp | ling_jiu_map (SUBSTR (float_part, k, 1 ));
End loop;

Returnvalue: = returnvalue | 'point' | tmp;
End if;

RETURN returnvalue;
END;

FUNCTION chi2num (chivalue IN VARCHAR2)
RETURN NUMBER
IS
-- Ing table of Chinese Character ing numbers
Type map is table of BINARY_INTEGER
Index by VARCHAR2 (10 );

Ling_jiu_map MAP;
Shibaiqian_map MAP;
Wanyizhao_map MAP;
-- Temporary Variable
I INT: = 0; -- 'temporary variable
J INT: = 0; -- Temporary Variable
K INT: = 0; -- Temporary Variable
X INT: = 0; -- Temporary Variable
Y INT: = 0; -- Temporary Variable
Str VARCHAR2 (100): = ''; -- Temporary Variable
Tmp INT: = 0; -- Temporary Variable
Tmp2 INT: = 0; -- Temporary Variable
Integer_part VARCHAR2 (100): = ''; -- integer part in the numerical form
Float_part VARCHAR2 (100): = ''; -- digit decimal part
Returnvalue NUMBER: = 0; -- Return Value
BEGIN -- Initialize an array
Ling_jiu_map ('0'): = 0;
Ling_jiu_map ('1'): = 1;
Ling_jiu_map ('2'): = 2;
Ling_jiu_map ('3'): = 3;
Ling_jiu_map ('4'): = 4;
Ling_jiu_map ('5'): = 5;
Ling_jiu_map ('6'): = 6;
Ling_jiu_map ('7'): = 7;
Ling_jiu_map ('8'): = 8;
Ling_jiu_map ('9'): = 9;
Shibaiqian_map (''): = 0;
Shibaiqian_map ('10'): = 1;
Shibaiqian_map ('bai'): = 2;
Shibaiqian_map ('kil'): = 3;
Wanyizhao_map (''): = 0;
Wanyizhao_map ('wan'): = 1;
Wanyizhao_map (' '): = 2;
Wanyizhao_map ('mb '): = 3;
-- Complete one of the top 10
Str: = REGEXP_REPLACE (chivalue, '^ 10', '10 ');
Str: = REGEXP_REPLACE (str, 'zero 10', 'zero 10 ');
-- Split the number into two numeric arrays: integer and floating-point.
I: = INSTR (str, 'point', 1 );

IF I = 0
THEN
Integer_part: = SUBSTR (str, 1 );
ELSE
Integer_part: = SUBSTR (str, 1, I-1 );
Float_part: = SUBSTR (str, I + 1 );
End if;

-- Integer
X: = LENGTH (integer_part );
Str: = '';
Tmp: = 0;
Tmp2: = 0;

FOR k IN 1. x
LOOP
Str: = SUBSTR (integer_part, k, 1 );

IF str IN ('000000', '000000', 'mega ')
THEN
-- When it comes to trillions of megabytes
Tmp: = tmp + tmp2;
Returnvalue: =
Returnvalue + tmp * POWER (10000, wanyizhao_map (str ));
Tmp: = 0;
Tmp2: = 0;
ELSIF str IN ('10', '100', 'kilo ')
THEN
-- When it hits
Tmp: = tmp + tmp2 * POWER (10, shibaiqian_map (str ));
Tmp2: = 0;
ELSE
-- When a number is reached
Tmp2: = ling_jiu_map (str );
End if;
End loop;

-- Add the number of digits not less than to the integer.
Tmp: = tmp + tmp2;
Returnvalue: = returnvalue + tmp;

IF float_part IS NOT NULL
THEN
-- Calculates the decimal part.
K: = 0;
Tmp: = '';

FOR k IN 1 .. LENGTH (float_part)
LOOP
Tmp: = tmp | ling_jiu_map (SUBSTR (float_part, k, 1 ));
End loop;

Returnvalue: = returnvalue | '.' | tmp;
End if;

RETURN returnvalue;
END;
END czutil;