CREATE OR REPLACE

FUNCTION "fn_CompareAge" (in_age1 IN VARCHAR2, in_age2 IN VARCHAR2)

RETURN INTEGER

AS

v_mark1 INTEGER := 1;

v_mark2 INTEGER := 1;

v_num1 INTEGER;

v_num2 INTEGER;

v_result Integer := 0;

BEGIN

IF "INSTR"(in_age1, 'D') > 0 THEN

v_mark1 := 3;

ELSIF INSTR(in_age1, 'M') > 0 THEN

v_mark1 := 2;

END IF;

IF INSTR(in_age2, 'D') > 0 THEN

v_mark2 := 3;

ELSIF INSTR(in_age2, 'M') > 0 THEN

v_mark2 := 2;

END IF;

IF v_mark1 <= v_mark2 THEN

IF v_mark1 = 3 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'D')+1, LENGTH(in_age1)));

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'D')+1, LENGTH(in_age2)));

END IF;

END IF;

ELSIF v_mark1 = 2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'M')+1, LENGTH(in_age1)));

IF (v_mark2 = 3) THEN

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

ELSIF v_mark2 = 2 THEN

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, LENGTH(in_age2)));

END IF;

END IF;

ELSIF v_mark1 = 1 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, LENGTH(in_age1)));

IF (v_mark2 = 2 or v_mark2 = 3) THEN

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

ELSIF v_mark2 = 1 THEN

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, LENGTH(in_age2)));

END IF;

END IF;

ELSIF v_mark1 > v_mark2 THEN

IF v_mark2 = 2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, LENGTH(in_age2)));

END IF;

ELSIF v_mark2 = 1 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, LENGTH(in_age2)));

END IF;

END IF;

IF v_num1 >= v_num2 THEN

v_result := 1;

END IF;

RETURN v_result;

END;