郵便番号CSVで３８文字制約で２行以上になっているものをSQLでまとめる (MySQL編）

# 一時テーブル（CSVと同じレイアウト)

```mysql: 
-- 主キーは持たせない
mysql> CREATE TEMPORARY TABLE zip_csv (
     >    CITY_CODE INT(5),
     >    ZIP5      CHAR(5),
     >    ZIP7      CHAR(7),
     >    PREF_R    TEXT,
     >    CITY_R    TEXT,
     >    TOWN_R    TEXT,
     >    PREF      TEXT,
     >    CITY      TEXT,
     >    TOWN      TEXT,
     >    FLAG01    CHAR(1),
     >    FLAG02    CHAR(1),
     >    FLAG03    CHAR(1),
     >    FLAG04    CHAR(1),
     >    FLAG05    CHAR(1),
     >    FLAG06    CHAR(1)
     > );
```

# データの取り込み

> 読み込み時に何かエラーが発生したら処々対応

```mysql:load-date-infile
mysql> LOAD DATA 
     > INFILE '01.hokkaido.txt' 
     > INTO TABLE zip_csv  
     > FIELDS TERMINATED BY ',' 
     > ;
```

# 複数行データを変換

> まずは基本の `(` が含まれていて `)`で終了していないデータ。
（これ以外があったら跡で追記する。

```mysql:procedure
mysql > delimiter ;;
mysql> CREATE PROCEDURE zip_proc_pre ()
    -> BEGIN
    ->  DECLARE X_CITY_CODE INT(5);
    ->  DECLARE X_ZIP5      VARCHAR(5);
    ->  DECLARE X_ZIP7      VARCHAR(7);
    ->  DECLARE X_PREF_R    TEXT;
    ->  DECLARE X_CITY_R    TEXT;
    ->  DECLARE X_TOWN_R    TEXT;
    ->  DECLARE X_TOWN_R_T  TEXT;
    ->  DECLARE X_PREF      TEXT;
    ->  DECLARE X_CITY      TEXT;
    ->  DECLARE X_TOWN      TEXT;
    ->  DECLARE X_TOWN_T    TEXT;
    ->  DECLARE X_FLAG01    CHAR(1);
    ->  DECLARE X_FLAG02    CHAR(1);
    ->  DECLARE X_FLAG03    CHAR(1);
    ->  DECLARE X_FLAG04    CHAR(1);
    ->  DECLARE X_FLAG05    CHAR(1);
    ->  DECLARE X_FLAG06    CHAR(1);
    ->  DECLARE XS_CITY_CODE INT(5);
    ->  DECLARE XS_ZIP7      VARCHAR(7);
    ->  DECLARE XS_TOWN_R TEXT;
    ->  DECLARE XS_TOWN   TEXT;
    ->  DECLARE CURSOR_ZIP_CSV_END INT(1) DEFAULT 0;
    ->  DECLARE CURSOR_ZIP_CSV CURSOR FOR 
    ->    SELECT * 
    ->    FROM zip_csv 
    ->    WHERE TOWN_R LIKE '%(%' AND NOT TOWN_R LIKE '%(%)';
    ->  DECLARE CURSOR_ZIP_CSV_2 CURSOR FOR 
    ->    SELECT x.TOWN_R, x.TOWN 
    ->    FROM zip_csv x 
    ->    WHERE x.CITY_CODE = X_CITY_CODE 
    ->      AND x.ZIP7 = X_ZIP7;
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET CURSOR_ZIP_CSV_END = 1;
    ->  OPEN CURSOR_ZIP_CSV ;
    ->  LOOP1: LOOP
    ->          FETCH CURSOR_ZIP_CSV INTO
    ->            X_CITY_CODE, 
    ->            X_ZIP5, X_ZIP7,
    ->            X_PREF_R, X_CITY_R, X_TOWN_R,
    ->            X_PREF,   X_CITY,   X_TOWN,
    ->            X_FLAG01, X_FLAG02,
    ->            X_FLAG03, X_FLAG04,
    ->            X_FLAG05, X_FLAG06;
    ->          IF CURSOR_ZIP_CSV_END THEN LEAVE LOOP1; END IF;
    ->          DELETE FROM zip_csv
    ->          WHERE CITY_CODE = X_CITY_CODE
    ->            AND ZIP5      = X_ZIP5
    ->            AND ZIP7      = X_ZIP7
    ->            AND PREF_R    = X_PREF_R
    ->            AND CITY_R    = X_CITY_R
    ->            AND TOWN_R    = X_TOWN_R
    ->            AND PREF      = X_PREF
    ->            AND CITY      = X_CITY
    ->            AND TOWN      = X_TOWN;
    ->          OPEN CURSOR_ZIP_CSV_2;
    ->          LOOP2: LOOP
    ->                  FETCH CURSOR_ZIP_CSV_2 INTO XS_TOWN_R, XS_TOWN;
    ->                  IF CURSOR_ZIP_CSV_END THEN
    ->                          SET CURSOR_ZIP_CSV_END = 0;
    ->                          CLOSE CURSOR_ZIP_CSV_2;
    ->                          LEAVE LOOP2;
    ->                  END IF;
    ->                  DELETE FROM zip_csv
    ->                  WHERE CITY_CODE = X_CITY_CODE
    ->                    AND ZIP5      = X_ZIP5
    ->                    AND ZIP7      = X_ZIP7
    ->                    AND PREF_R    = X_PREF_R
    ->                    AND CITY_R    = X_CITY_R
    ->                    AND TOWN_R    = XS_TOWN_R
    ->                    AND PREF      = X_PREF
    ->                    AND CITY      = X_CITY
    ->                    AND TOWN      = XS_TOWN;
    ->                  SET X_TOWN_R = CONCAT(X_TOWN_R, XS_TOWN_R);
    ->                  SET X_TOWN   = CONCAT(X_TOWN  , XS_TOWN  );
    ->                  IF X_TOWN_R LIKE '%(%)' THEN
    ->                          INSERT INTO zip_csv VALUES(
    ->                                  X_CITY_CODE, 
    ->                                  X_ZIP5, X_ZIP7, 
    ->                                  X_PREF_R, X_CITY_R, X_TOWN_R, 
    ->                                  X_PREF,   X_CITY,   X_TOWN, 
    ->                                  X_FLAG01, X_FLAG02, 
    ->                                  X_FLAG03, X_FLAG04, 
    ->                                  X_FLAG05, X_FLAG06
    ->                          );
    ->                          SET X_TOWN_R = '';
    ->                          SET X_TOWN   = '';
    ->                          ITERATE LOOP2;
    ->                  END IF;
    ->          END LOOP;
    ->  END LOOP;
    -> END;
    -> ;;
mysql> delimiter ;
```

# 大通西（１～１９丁目）のような地域を分割する。

```mysql
mysql> delimiter ;;
mysql> CREATE PROCEDURE zip_proc_chome_cus (
     >   IN A_TOWN_R VARCHAR(10), 
     >   IN A_TOWN VARCHAR(10)
     > )
     > BEGIN
     >   DECLARE X_CITY_CODE INT(5);
     >   DECLARE X_ZIP5      VARCHAR(5);
     >   DECLARE X_ZIP7      VARCHAR(7);
     >   DECLARE X_PREF_R    TEXT;
     >   DECLARE X_CITY_R    TEXT;
     >   DECLARE X_TOWN_R    TEXT;
     >   DECLARE X_TOWN_R_T  TEXT;
     >   DECLARE X_TOWN_R_T2 TEXT;
     >   DECLARE X_PREF      TEXT;
     >   DECLARE X_CITY      TEXT;
     >   DECLARE X_TOWN      TEXT;
     >   DECLARE X_TOWN_T    TEXT;
     >   DECLARE X_FLAG01    CHAR(1);
     >   DECLARE X_FLAG02    CHAR(1);
     >   DECLARE X_FLAG03    CHAR(1);
     >   DECLARE X_FLAG04    CHAR(1);
     >   DECLARE X_FLAG05    CHAR(1);
     >   DECLARE X_FLAG06    CHAR(1);
     >   DECLARE LOOP_COUNT  INT;
     > 
     >   DECLARE BEG INT DEFAULT  0;
     >   DECLARE LAS INT DEFAULT -1;
     >   
     >   DECLARE CURSOR_ZIP_CSV_END INT(1) DEFAULT 0;
     >   DECLARE CURSOR_ZIP_CSV CURSOR FOR 
     >     SELECT * 
     >     FROM zip_csv 
     >     WHERE TOWN_R REGEXP 
     >        CONCAT('^.+[(][0-9]+-[0-9]+',A_TOWN_R,'[)]$')
     > 	   OR TOWN_R REGEXP 
     >        CONCAT('^.+[(][0-9]+',A_TOWN_R,'-[0-9]+',A_TOWN_R,'[)]$')
     > ;
     >   DECLARE CONTINUE HANDLER FOR 
     >        NOT FOUND SET CURSOR_ZIP_CSV_END = 1;
     >   
     >   OPEN CURSOR_ZIP_CSV ;
     >   LOOP1: LOOP  
     >     FETCH CURSOR_ZIP_CSV INTO
     >       X_CITY_CODE, 
     >       X_ZIP5, X_ZIP7, 
     >       X_PREF_R, X_CITY_R, X_TOWN_R, 
     >       X_PREF,   X_CITY,   X_TOWN , 
     >       X_FLAG01, X_FLAG02, X_FLAG03, 
     >       X_FLAG04, X_FLAG05, X_FLAG06;
     >     
     >     IF CURSOR_ZIP_CSV_END THEN LEAVE LOOP1; END IF;
     >     
     >     SET X_TOWN_R_T = 
     >        SUBSTR(X_TOWN_R, 1, INSTR(X_TOWN_R, '(') -1);
     >     SET X_TOWN_T   = 
     >        SUBSTR(X_TOWN  , 1, INSTR(X_TOWN  , '（') -1);
     >     
     >     DELETE FROM zip_csv 
     >     WHERE
     >         CITY_CODE = X_CITY_CODE
     >         AND ZIP5      = X_ZIP5 
     >         AND ZIP7      = X_ZIP7
     >         AND PREF_R    = X_PREF_R
     >         AND CITY_R    = X_CITY_R
     >         AND TOWN_R    = X_TOWN_R
     >         AND PREF      = X_PREF
     >         AND CITY      = X_CITY
     >         AND TOWN      = X_TOWN;
     > 
     > 	SET X_TOWN_R_T2 = REPLACE(X_TOWN_R, CONCAT(X_TOWN_R_T, '('), '');
     > 	SET X_TOWN_R_T2 = REPLACE(X_TOWN_R_T2, A_TOWN_R, '');
     > 	SET X_TOWN_R_T2 = REPLACE(X_TOWN_R_T2, ')', ''); -- 1-29
     > 	
     > 	SET BEG = CONVERT(SUBSTR(X_TOWN_R_T2, 1, INSTR(X_TOWN_R_T2, '-')-1), SIGNED); 
     > 	SET LAS = CONVERT(SUBSTR(X_TOWN_R_T2, INSTR(X_TOWN_R_T2, '-')+1), SIGNED); 
     > 
     >     SET LOOP_COUNT = BEG;
     >     WHILE LOOP_COUNT < LAS
     >     DO
     >       INSERT INTO zip_csv
     >         SELECT
     >           X_CITY_CODE, 
     >           X_ZIP5, X_ZIP7, 
     >           X_PREF_R, X_CITY_R, CONCAT(X_TOWN_R_T, LOOP_COUNT, A_TOWN_R), 
     >           X_PREF,   X_CITY,   CONCAT(X_TOWN_T  , LOOP_COUNT, A_TOWN),  
     >           X_FLAG01, X_FLAG02, X_FLAG03, 
     >           X_FLAG04, X_FLAG05, X_FLAG06;
     >         SET LOOP_COUNT = LOOP_COUNT + 1;
     >     END WHILE;
     >   END LOOP;
     > END;
     > ;;
mysql> delimiter ;
mysql> CALL zip_proc_chome_cus('ﾁﾖｳﾒ', '丁目');
mysql> CALL zip_proc_chome_cus('ﾊﾞﾝﾁ', '番地');
```