Oracle实例之---- 身份证校验

SELECT SUBSTR('&AI', 0, 17) ||
TRANSLATE(DECODE(MOD(SUM(SUBSTR(WI,
REGEXP_INSTR(WI, ' ', 1, I) + 1,
REGEXP_INSTR(WI, ' ', 1, I + 1) -
REGEXP_INSTR(WI, ' ', 1, I) - 1) *
SUBSTR(AI, I, 1)),
11),
10,
'a',
MOD(SUM(SUBSTR(WI,
REGEXP_INSTR(WI, ' ', 1, I) + 1,
REGEXP_INSTR(WI, ' ', 1, I + 1) -
REGEXP_INSTR(WI, ' ', 1, I) - 1) *
SUBSTR(AI, I, 1)),
11)),
'0123456789a',
'10X98765432')
FROM (SELECT '&AI' AI,
' 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 ' WI,
ROWNUM I
FROM DUAL
CONNECT BY ROWNUM < 18);

 

 

SELECT SUBSTR('&AI', 0, 17) ||
TRANSLATE(DECODE(MOD(SUM(MOD(POWER(2, 18 - I), 11) *
SUBSTR(AI, I, 1)),
11),
10,
'a',
MOD(SUM(MOD(POWER(2, 18 - I), 11) * SUBSTR(AI, I, 1)),
11)),
'0123456789a',
'10X98765432')
FROM (SELECT '&AI' AI, ROWNUM I FROM DUAL CONNECT BY ROWNUM < 18);

 

 

SELECT SUBSTR('&AI', 0, 17) ||
REPLACE(MOD(12 -
MOD(SUM(MOD(POWER(2, 18 - I), 11) * SUBSTR(AI, I, 1)),
11),
11),
'10',
'X')
FROM (SELECT '&AI' AI, ROWNUM I FROM DUAL CONNECT BY ROWNUM < 18);