[MS-SQL] NAME 이름 MASKING 마스킹

▶[MS-SQL] NAME 이름 MASKING 마스킹 

 

-- 함수생성
CREATE FUNCTION [dbo].[FN_MASK_NAME]
(
	@STR VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
     BEGIN
         DECLARE @RTNVALUE VARCHAR(50), @STRLEN INT;
         IF LEN(@STR) = 2
             BEGIN
                 SET @RTNVALUE = LEFT(@STR, 1) + '*';
         END;
             ELSE
             IF LEN(@STR) >= 3
                 BEGIN
                     SET @STRLEN = LEN(@STR) - 2;
                     SET @RTNVALUE = LEFT(@STR, 1) + REPLACE(SPACE(@STRLEN), ' ', '*') + RIGHT(@STR, 1);
             END;
                 ELSE
                 BEGIN
                     SET @RTNVALUE = @STR;
             END;
         RETURN @RTNVALUE;
     END;


-- 함수 결과
select DBO.FN_MASK_NAME('홀길동')   -- 홍*동
select DBO.FN_MASK_NAME('홀길길동')   -- 홍**동


-- 함수를 사용하지 않은 방법
SELECT SUBSTRING(U.userName, 0, 2) + CASE
                                         WHEN LEN(U.userName) = '2'
                                         THEN '*'
                                         ELSE REPLICATE('*', LEN(U.userName) - 2) + SUBSTRING(U.userName, LEN(U.userName), LEN(U.userName) + 1)
                                     END AS userName
FROM
(
    SELECT '가나다' AS userName   -- 가*다
    UNION ALL
    SELECT '가나다라마' AS userName  -- 가***마
) U;