[mysql] 특정 구분자로 합쳐진 데이터 분리하기

[comment] 

업무 번외로 받은 첫번째 과제..! 

특정 구분자로 이어져서 들어오는 데이터를 분리하여 테이블에 입력하는 프로시저 작성하기.

만들고 나니 간단한 프로시저인데.. 업무 하루를 꼬박 날렸다..

책에서 접한 기본적인 프로시저 테스트만 해본 상태라서.. 직접 짜보려니 어려웠다 ㅠ_ㅠ

---

특정 구분자로 합쳐진 데이터 분리하기 (구분자로 이어진 데이터 분리)

1. 요청예시 및 구상

a,b,c,d,... 

a|b|cde||f,... 

c$f$gg$e$...

위와 같이 다양한 형태 구분자로 합쳐져 있는 데이터 분리하기.

- 작업 구상

1) 데이터의 총 개수 구하기 -> 구분자 +1 만큼 데이터의 총 개수

2) 전체 데이터의 개수만큼 while문 이용하여 데이터 추출  

2. 테이블 생성 : 얻게 되는 데이터를 입력할 테이블

create table GET_DATA(getData varchar(20));

3. 총 데이터 개수 구하기 (구분자가 1bytes일 때)

SELECT CAST((LENGTH('a|b|cde||f')-LENGTH(REPLACE('a|b|cde||f','|','')) +1 ) as unsigned);

# LENGTH('a|b|cde||f') -- 입력받은 데이터의 전체 bytes

# LENGTH(REPLACE('a|b|cde||f','|','')  -- 입력받은 구분자(IN_DELI)를 제외한 데이터의 bytes = 구분자 개수 

+ 함수설명

1. LENGTH  # 문자열의 길이를 bytes로 나타낸다.

사용법 : LENGTH() 

SELECT LENGTH('한글') -> 6    # utf-8의 경우 한글은 한글자당 3bytes, 영어는 1bytes 

SELECT LENGTH('abc')  -> 3

2. CHAR_LENGTH() : 글자의 수를 나타낸다.

SELECT CHAR_LENGTH('한글') -> 2

SELECT CHAR_LENGTH('abc') -> 3

3. BIT_LENGTH() : 길이를 bit로 나타낸다.

SELECT BIT_LENGTH('한글a') -> 56

SELECT BIT_LENGTH('한글')   -> 48

출처: http://nejary.tistory.com/entry/MySQL-문자열-길이-한글 [세상 품기]

4. 프로시저 생성 

DELIMITER //        

CREATE PROCEDURE SP_GET_DATA(

IN IN_DATA VARCHAR(30),  # 구분자로 이어진 데이터

IN IN_DELI VARCHAR(2),

IN IN_TB_NAME VARCHAR(30)

)

BEGIN 

   DECLARE POS INT;    # 데이터 총 개수

   DECLARE i INT default 1;    # 데이터 순번 

   DECLARE OUT_DATA VARCHAR(20);    # 얻어진 각 데이터

# 총 데이터의 사이즈에서 구분자를 제외한 길이 

SELECT CAST((LENGTH(IN_DATA)-LENGTH(REPLACE(IN_DATA,IN_DELI,'')) +1 ) as unsigned) into POS;

while i <= POS DO

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IN_DATA,IN_DELI,i),IN_DELI,-1) into OUT_DATA;

SET @_SQL = CONCAT("insert into ", IN_TB_NAME," values('", OUT_DATA,"');");

PREPARE myquery FROM @_SQL;

EXECUTE myquery;

commit;

set i = i + 1;

END while;

END //

5. 프로시저 실행

CALL SP_GET_DATA('a|b|cde||f','|','GET_DATA');  # 구분자로 이어진 데이터, 구분자, 테이블명을 입력하여 프로시저 호출 

SELECT * FROM GET_DATA;                         # 결과확인 

결과