[MS-SQL] 주민번호 유효성 체크 함수
스칼라 변환 함수에 사용하자
사용예) dbo.fn_RES_NO_CHK(주민_법인번호) = ‘0’
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [dbo].[fn_RES_NO_CHK](@NUM CHAR(13))
RETURNS CHAR(13)
BEGIN
DECLARE @RETURN CHAR(1), @VALUE INT
SELECT @RETURN='0'
IF @NUM NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
RETURN @RETURN
SELECT @VALUE=0
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,1,1)*2
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,2,1)*3
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,3,1)*4
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,4,1)*5
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,5,1)*6
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,6,1)*7
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,7,1)*8
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,8,1)*9
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,9,1)*2
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,10,1)*3
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,11,1)*4
SELECT @VALUE=@VALUE+SUBSTRING(@NUM,12,1)*5
SELECT @VALUE=(11-(@VALUE%11))%10
IF(CONVERT(CHAR(1),@VALUE)=SUBSTRING(@NUM,13,1)
AND ISDATE(CONVERT(CHAR(2),(((CONVERT(INT,SUBSTRING(@NUM,7,1))+1)%10)/2+18))+SUBSTRING(@NUM,1,6))=1
AND SUBSTRING(@NUM,7,1) IN ('0','1','2','3','4','9') AND LEN(@NUM)=13)
BEGIN
SELECT @RETURN='1'
END
RETURN @RETURN
END
GO
