公民身份证清洗

CREATE OR REPLACE FUNCTION FN_GZK_IDCARD
(
IN_V1 VARCHAR2,
IN_V2 VARCHAR2
) RETURN VARCHAR2 IS
STR_A VARCHAR2(200);
OUT_A VARCHAR2(200);
LEN_A NUMBER;
BEGIN
STR_A := REGEXP_REPLACE(UPPER(TO_SINGLE_BYTE(TO_CHAR(IN_V1))), ‘[^A-Z0-9]’);
LEN_A := LENGTH(STR_A);
OUT_A := CASE
WHEN NOT REGEXP_LIKE(STR_A, ‘[0-9]’) THEN
NULL
WHEN FN_CHECKIDCARD(STR_A) = 1 THEN
IDCARD15TO18(STR_A)
WHEN LEN_A = 17
AND FN_CHECKIDCARD(STR_A || ‘X’) = 1 THEN
STR_A || ‘X’
WHEN LEN_A > 18
AND FN_CHECKIDCARD(SUBSTR(STR_A, LEN_A - 18 + 1)) = 1 THEN
SUBSTR(STR_A, LEN_A - 18 + 1)
WHEN LEN_A > 18
AND FN_CHECKIDCARD(SUBSTR(STR_A, 1, 18)) = 1 THEN
SUBSTR(STR_A, 1, 18)
WHEN REGEXP_REPLACE(STR_A, ‘[0]+’) IS NULL THEN
NULL
WHEN NVL(IN_V2, ‘’) != 1
AND STR_A IS NOT NULL THEN
REGEXP_REPLACE(UPPER(TO_SINGLE_BYTE(TO_CHAR(IN_V1))), ‘[[:punct:][:space:][:cntrl:]]’, ‘’)
WHEN IN_V2 = 1
AND FN_CHECKIDCARD(STR_A) != 1 THEN
NULL
ELSE
REGEXP_REPLACE(STR_A, ‘[[:punct:][:space:][:cntrl:]]’, ‘’)
END;

RETURN OUT_A;

END FN_GZK_IDCARD;