[MS SQL] 주민등록번호 암호화/복호화 펑션

* 암호화

/*
SELECT [dbo].[F_ENCRYPT](JUMINID)
*/ 
CREATE Function [dbo].[F_ENCRYPT]
(    
    @InTxt VARCHAR(100)
) 
RETURNS varbinary(1000)
AS
BEGIN
    DECLARE @RESULT varbinary(1000)
     
    
    
    SELECT @RESULT =  ENCRYPTBYPASSPHRASE('SHA2_256',@InTxt)
     

    RETURN @RESULT
END

go

 

* 복호화

/*
SHA2_256으로 변환 복호화 함수
SELECT [dbo].[F_DECRYPT](JUMINID3)
*/ 
CREATE Function [dbo].[F_DECRYPT]
(    
    @OutTxt varbinary(1000) 
) 
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @RESULT VARCHAR(100)      
    SELECT @RESULT =  CONVERT(VARCHAR(100),DECRYPTBYPASSPHRASE('SHA2_256',@OutTxt))     
    RETURN @RESULT
END

go