[mariaDB] 휴대폰번호 '-' 하이픈 함수 만들기

 

ex) 휴대폰 번호가 담겨있는 컬럼에 01012345678 이렇게 값이 있을 경우

화면에 출력을 010-1234-5678로 해야 한다.

 

그냥 쿼리에서 바로 출력한다면 concat과 substring으로 출력할 수있지만 

실무에서는 함수로 만들어 사용하는 경우가 많기 때문에 함수로 만들어 보려고 한다. 

 

 

CONCAT(SUBSTRING(컬럼명 from 1 for 3), '-' ,SUBSTRING(컬럼명 from 4 for 4), '-' ,SUBSTRING(컬럼명 from 8 for 4)) as PHN_NO

-> 쿼리로 출력하는 경우

 

 

 

 

DELIMITER // CREATE FUNCTION mask (unformatted_value BIGINT, format_string CHAR(32)) RETURNS CHAR(32) DETERMINISTIC BEGIN # Declare variables DECLARE input_len TINYINT; DECLARE output_len TINYINT; DECLARE temp_char CHAR; # Initialize variables SET input_len = LENGTH(unformatted_value); SET output_len = LENGTH(format_string); # Construct formated string WHILE ( output_len > 0 ) DO SET temp_char = SUBSTR(format_string, output_len, 1); IF ( temp_char = '#' ) THEN IF ( input_len > 0 ) THEN SET format_string = INSERT(format_string, output_len, 1, SUBSTR(unformatted_value, input_len, 1)); SET input_len = input_len - 1; ELSE SET format_string = INSERT(format_string, output_len, 1, '0'); END IF; END IF; SET output_len = output_len - 1; END WHILE; RETURN format_string; END // DELIMITER ;

 

 

mysql> select mask(123456789,'###-##-####');  +-------------------------------+  | mask(123456789,'###-##-####') |  +-------------------------------+  | 123-45-6789                   |  +-------------------------------+  1 row in set (0.00 sec)  mysql> select mask(123456789,'(###) ###-####');  +----------------------------------+  | mask(123456789,'(###) ###-####') |  +----------------------------------+  | (012) 345-6789                   |  +----------------------------------+  1 row in set (0.00 sec)  mysql> select mask(123456789,'###-#!##@(###)');  +----------------------------------+  | mask(123456789,'###-#!##@(###)') |  +----------------------------------+  | 123-4!56@(789)                   |  +----------------------------------+

위의 함수로 만들면 여러 가지 형태로 휴대폰 번호를 출력해낼 수 있다.

 

하지만, 테이블 리스트에서 휴대폰 번호의 컬럼 값이 한 행이라도 null or 빈 값이 들어가 있다면

결과값이 이상하게 출력되었다. 

 

 

 

따라서, 다시 검색한 결과,

 

리스트 중 한 행의 휴대폰 번호 컬럼에 null or 빈 값이 들어가 있어도

null or 빈 값의 행은 null or 빈 값으로 나오고 다른 행들은 정상적이게 출력되는 함수를 찾았다.

delimiter $$  CREATE FUNCTION FN_GET_PHN_NO( pno VARCHAR(50) ) RETURNS VARCHAR(50)  BEGIN  DECLARE p_no VARCHAR(50);  case length(pno)           when 9 then  set p_no = concat( left(pno,2), '-', mid(pno,3,3), '-', right(pno,4) );  when 10 then  case left(pno,2)  when '02' then  set p_no = concat( left(pno,2), '-', mid(pno,3,4), '-', right(pno,4) );  else  set p_no = concat( left(pno,3), '-', mid(pno,4,3), '-', right(pno,4) );  end case;  when 11 then  set p_no = concat( left(pno,3), '-', mid(pno,4,4), '-', right(pno,4) );  when 12 then  set p_no = concat( left(pno,4), '-', mid(pno,4,4), '-', right(pno,4) );  else set p_no = pno;  end case;  RETURN p_no;  END $$  delimiter ;

실행 쿼리 : SELECT FN_GET_PHN_NO(컬럼명) from 테이블명 

 

이 함수는 case문으로 앞자리가 02인 지역번호, 휴대폰 번호 개수별로 출력할 수 있다. 

 

 

ex)

 

 

* Y이면 마스킹, N이면 숫자 보여주기 함수

ex) FN_GET_MASK_PHN_NO('변경할 컬럼명', 'Y') -> 마스킹

    FN_GET_MASK_PHN_NO('변경할 컬럼명', 'N') -> 마스킹(X)

CREATE FUNCTION FN_GET_MASK_PHN_NO (pno VARCHAR(50), mask char(1) ) RETURNS varchar(50) CHARSET utf8  BEGIN  DECLARE p_no VARCHAR(50);  case length(pno)   when 9 then  if mask = 'Y' then  set p_no = concat( left(pno,2), '-', '***', '-', right(pno,4) );  else   set p_no = concat( left(pno,2), '-', mid(pno,3,3), '-', right(pno,4) );  end if;  when 10 then  case left(pno,2)  when '02' then  if mask = 'Y' then  set p_no = concat( left(pno,2), '-', '****', '-', right(pno,4) );  else   set p_no = concat( left(pno,2), '-', mid(pno,3,4), '-', right(pno,4) );  end if;  else  if mask = 'Y' then  set p_no = concat( left(pno,3), '-', '***', '-', right(pno,4) );  else   set p_no = concat( left(pno,3), '-', mid(pno,4,3), '-', right(pno,4) );  end if;  end case;  when 11 then  if mask = 'Y' then  set p_no = concat( left(pno,3), '-', '****', '-', right(pno,4) );  else   set p_no = concat( left(pno,3), '-', mid(pno,4,4), '-', right(pno,4) );  end if;  when 12 then  if mask = 'Y' then  set p_no = concat( left(pno,4), '-', '****', '-', right(pno,4) );  else   set p_no = concat( left(pno,4), '-', mid(pno,4,4), '-', right(pno,4) );  end if;  else   set p_no = pno;  end case;  RETURN p_no;  end