Oracle Chinese numerals to Arabic numerals
CREATE OR REPLACE FUNCTION chi2num (chivalue IN VARCHAR2) RETURN NUMBER IS
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
K INT: = 0;-- temporary variable
X INT: = 0;-- temporary variable
Str VARCHAR2: ='';-temporary variable
Tmp INT: = 0;-- temporary variable
Tmp2 INT: = 0;-- temporary variable
Integer_part VARCHAR2: ='';-- Integer part in numeric form
Float_part VARCHAR2: ='';-- decimal part in numeric form
Returnvalue NUMBER: = 0;-- return value
BEGIN
-initialize the array
Ling_jiu_map ('zero'): = 0
Ling_jiu_map ('one'): = 1
Ling_jiu_map ('2'): = 2
Ling_jiu_map ('3'): = 3
Ling_jiu_map ('four'): = 4
Ling_jiu_map ('five'): = 5
Ling_jiu_map ('six'): = 6
Ling_jiu_map ('7'): = 7
Ling_jiu_map ('eight'): = 8
Ling_jiu_map ('nine'): = 9
Shibaiqian_map ('): = 0
Shibaiqian_map ('x'): = 1
Shibaiqian_map ('hundred'): = 2
Shibaiqian_map ('thousands'): = 3
Wanyizhao_map ('): = 0
Wanyizhao_map ('million'): = 1
Wanyizhao_map ('billion'): = 2
Wanyizhao_map ('trillion'): = 3
Str: = REGEXP_REPLACE (chivalue,'^ 10','10')
Str: = REGEXP_REPLACE (str, 'zero ten','10')
Split a number into an array of integers and floating-point numbers
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 part
X: = LENGTH (integer_part)
Str: =''
Tmp: = 0
Tmp2: = 0
FOR k IN 1.. X LOOP
Str: = SUBSTR (integer_part, k, 1)
IF str IN ('million', 'billion', 'trillion') THEN
When it comes to trillions
Tmp: = tmp + tmp2
Returnvalue: = returnvalue + tmp * POWER (10000, wanyizhao_map (str))
Tmp: = 0
Tmp2: = 0
ELSIF str IN ('ten', 'hundred', 'thousand') THEN
-- when you meet a hundred thousand
Tmp: = tmp + tmp2 * POWER (10, shibaiqian_map (str))
Tmp2: = 0
ELSE
When it comes to numbers
Tmp2: = ling_jiu_map (str)
END IF
END LOOP
Finally, no less than ten thousand digits will be added to the integer.
Tmp: = tmp + tmp2
Returnvalue: = returnvalue + tmp
IF float_part IS NOT NULL THEN
-- calculating 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
