Oracle function to get the age in IDCARD

The first generation of IDCARD is 15-digit numbers, and the second generation is 18-digit numbers.

The meaning of the new 18-digit IDCARD number: 1-2 digits code for provinces, autonomous regions and municipalities directly under the Central Government

3-4 prefecture-level city, league and autonomous prefecture codes

5-6 county, county city and district codes

7-14 date of birth, for example, 19670401 represents April 1, 1967

15-17 digits are serial numbers, of which 17 males are odd and females are even.

18 bits are parity codes, 0-9 and X, which are randomly generated by the formula.

Meaning of 15-digit IDCARD number: 1-2 digits code of province, autonomous region and municipality directly under the Central Government

3-4 prefecture-level city, league and autonomous prefecture codes

5-6 county, county city and district codes

7-12-digit date of birth, for example, 670401 represents April 1, 1967, which is the first difference from an 18-digit number

13-15 digits are serial numbers, of which 15 males are singular and females are even.

The second difference from the 18-digit * * symbol: there is no CAPTCHA for the last digit

To obtain the age information of the user from IDCARD, it is necessary to verify the validity of IDCARD.

-- determine whether it is a number, return True,FalseCREATE OR REPLACE FUNCTION DT_ISNUMBER (P_IN VARCHAR2) RETURN BOOLEAN AS I NUMBER; BEGIN I: = TO_NUMBER (P_IN); RETURN TRUE;EXCEPTION WHEN OTHERS THEN RETURN FALSE;END;-- determine whether it is a date, return True,FalseCREATE OR REPLACE FUNCTION DT_ISDATE (P_IN VARCHAR2) RETURN BOOLEAN AS I DATE;BEGIN I: = TO_DATE; RETURN TRUE;EXCEPTION WHEN OTHERS THEN RETURN FALSE;END -- determine whether it is a legitimate CREATE OR REPLACE FUNCTION DT_ISIDCARD (P_IDCARD VARCHAR2) RETURN BOOLEAN IS IDCARDLEN INTEGER;BEGIN IDCARDLEN: = LENGTH (P_IDCARD) IF (IDCARDLEN = 18 AND DT_ISNUMBER (SUBSTR (P_IDCARD, 1, IDCARDLEN-1)) AND DT_ISDATE (SUBSTR (P_IDCARD, 7,8)) OR (IDCARDLEN = 15 AND DT_ISNUMBER (SUBSTR (P_IDCARD, 1, IDCARDLEN)) AND DT_ISDATE ('19' | | SUBSTR (P_IDCARD, 7, 6)) THEN RETURN TRUE; ELSE RETURN FALSE; END IF;END DT_ISIDCARD -- get the age of the information on * and return CREATE OR REPLACE FUNCTION DT_GETAGE (P_IDCARD VARCHAR2) RETURN INTEGER ISIDCARDLEN INTEGER;IDCARDYEAR INTEGER;BEGIN IDCARDLEN: = LENGTH (P_IDCARD); IF DT_ISIDCARD (P_IDCARD) AND IDCARDLEN = 18 THEN IDCARDYEAR: = TO_NUMBER (SUBSTR (P_IDCARD) AND IDCARDLEN = 15 THEN IDCARDYEAR: = TO_NUMBER ('19' | SUBSTR)); END IF RETURN TO_NUMBER (TO_CHAR (SYSDATE,'YYYY'))-IDCARDYEAR; END DT_GETAGE

The above makes a record of the common functions used to obtain the age of * *, which is convenient for direct use in the future or for further analysis of other addresses and genders in * *.

(PS: why is the word shenfenzheng filtered out as sensitive information)