주민번호오류 체크

2011-07-28 19:28:30

---

SQL 4 Oracle 오라클용 sql 실무

▒ 주민번호오류 체크

select 주민번호, 
  translate(주민번호, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') ,
  translate(주민번호, '0123456789'||주민번호, '0123456789'),
 (case when
        MOD(11 -
            MOD(
                substr(주민번호,1,1) * 2 +
                substr(주민번호,2,1) * 3 +
                substr(주민번호,3,1) * 4 +
                substr(주민번호,4,1) * 5 +
                substr(주민번호,5,1) * 6 +
                substr(주민번호,6,1) * 7 +
                substr(주민번호,7,1) * 8 +
                substr(주민번호,8,1) * 9 +
                substr(주민번호,9,1) * 2 +
                substr(주민번호,10,1) * 3 +
                substr(주민번호,11,1) * 4 +
                substr(주민번호,12,1) * 5
            ,11)
          , 10)  = substr(주민번호,13,1)  
       then 'T'
       else 'F'
       end)
from 주민번호_Table