SQLServerで郵便番号ファイルを使えるようにしよう #2

（追記　2025/04/06）
つづき

https://qiita.com/Inada_Tomonori/items/ed6cbee3a374d6823ea1


## 前回の内容は
* 郵便番号データにはデータベース的に利用するには問題がある。
* この記事では問題を解決する経緯を見せるもので、解決方法がないこともありうる。

## まずはデータを登録しよう

議論を具体的に進めるために、郵便番号データをSQLServerに登録します。

### 郵便番号データの取得と展開

郵便番号データは、以下のリンクをクリックするとダウンロードできます。

郵便番号データ（SJIS、全国一括、2025年3月時点　1,694,353Byte）
[https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip](https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip)

ken_all.zipを展開するとKEN_ALL.CSSVが作成されます。

### SQLServerへの投入

#### SQLServerの設定

SQLServerは次のような設定とします。

* バージョン：　SQLServer2019
* OS:　Windows11
* サーバアドレス：　(local)
* インスタンス名：　MSSQLSERVER
* 認証方法；　SQL認証
* ユーザ名：　user
* パスワード：　pass
* 対象データベース：　COMMON_POSTCODE

注意：
以下にスクリプトなどを記述しますが、不要なオプションをしているところがあります。
ですが、私の環境では問題なく実行できていますので問題ないと思います。

#### テーブルの作成
テーブルはSqlcmdでバッチファイルから作成します。

``` テーブル作成.sql
if object_id('WkTbl_共通_郵便番号_Original') is not null drop table dbo.WkTbl_共通_郵便番号_Original;

CREATE TABLE [dbo].[WkTbl_共通_郵便番号_Original](
	uid int identity(1,1) not null primary key,
	[全国地方公共団体コード] [nvarchar](10) NOT NULL,
	[旧郵便番号] [nvarchar](10) NOT NULL,
	[郵便番号] [nvarchar](15) NOT NULL,
	[都道府県カナ] [nvarchar](15) NOT NULL,
	[市区町村カナ] [nvarchar](30) NOT NULL,
	[町域カナ] [nvarchar](100) NOT NULL,
	[都道府県名] [nvarchar](10) NOT NULL,
	[市区町村名] [nvarchar](20) NOT NULL,
	[町域名] [nvarchar](100) NOT NULL,
	[一町域が二以上の郵便番号で表される場合の表示] [bit] NOT NULL,
	[小字毎に番地が起番されている町域の表示] [bit] NOT NULL,
	[丁目を有する町域の場合の表示] [bit] NOT NULL,
	[一つの郵便番号で二以上の町域を表す場合の表示] [bit] NOT NULL,
	[更新の表示] [tinyint] NOT NULL,
	[変更理由] [tinyint] NOT NULL
) ON [PRIMARY]
;
```

``` テーブル作成.bat
sqlcmd -S (local)\MSSQLSERVER -d COMMON_POSTCODE -U user -P pass -I -Y0 -y0 -b -i テーブル作成.sql -o "log_テーブル作成.sql.log"
```

#### データの投入

##### フォーマットファイルの作成
bcpコマンドを使ってテーブルからフォーマットファイルを作成し、少しフォーマットファイルを改変してからbcpコマンドでデータを投入します。

``` フォーマットファイル作成.bat
bcp COMMON_POSTCODE.WkTbl_共通_郵便番号_Original format nul /S (local)\MSSQLSERVER -U user -P pass -c -C 932 -f format_WkTbl_共通_郵便番号_Original.fmt -t,
```

##### フォーマットファイルの書換
KEN_ALL.CSSVは文字列部分がダブルクォートで囲まれていることと、テーブルの先頭の列はidentityによる連番を自動生成する列なので、それに合うようにフォーマットファイルを書き換えます。

``` format_dbo.WkTbl_共通_郵便番号_Original.fmt （変更前）
12.0
15
1       SQLCHAR             0       20      ","      1     全国地方公共団体コード                           Japanese_CI_AS
2       SQLCHAR             0       20      ","      2     旧郵便番号                                       Japanese_CI_AS
3       SQLCHAR             0       30      ","      3     郵便番号                                         Japanese_CI_AS
4       SQLCHAR             0       30      ","      4     都道府県カナ                                     Japanese_CI_AS
5       SQLCHAR             0       60      ","      5     市区町村カナ                                     Japanese_CI_AS
6       SQLCHAR             0       200     ","      6     町域カナ                                         Japanese_CI_AS
7       SQLCHAR             0       20      ","      7     都道府県名                                       Japanese_CI_AS
8       SQLCHAR             0       40      ","      8     市区町村名                                       Japanese_CI_AS
9       SQLCHAR             0       200     ","      9     町域名                                           Japanese_CI_AS
10      SQLCHAR             0       1       ","      10    一町域が二以上の郵便番号で表される場合の表示     ""
11      SQLCHAR             0       1       ","      11    小字毎に番地が起番されている町域の表示           ""
12      SQLCHAR             0       1       ","      12    丁目を有する町域の場合の表示                     ""
13      SQLCHAR             0       1       ","      13    一つの郵便番号で二以上の町域を表す場合の表示     ""
14      SQLCHAR             0       5       ","      14    更新の表示                                       ""
15      SQLCHAR             0       5       "\r\n"   15    変更理由                                         ""
```

書き換えたあとはこうなります。

``` format_dbo.WkTbl_共通_郵便番号_Original.fmt (変更後)
12.0
15
1       SQLCHAR             0       20      ",\""      2     全国地方公共団体コード                           Japanese_CI_AS
2       SQLCHAR             0       20      "\",\""      3     旧郵便番号                                       Japanese_CI_AS
3       SQLCHAR             0       30      "\",\""      4     郵便番号                                         Japanese_CI_AS
4       SQLCHAR             0       30      "\",\""      5     都道府県カナ                                     Japanese_CI_AS
5       SQLCHAR             0       60      "\",\""      6     市区町村カナ                                     Japanese_CI_AS
6       SQLCHAR             0       200     "\",\""      7     町域カナ                                         Japanese_CI_AS
7       SQLCHAR             0       20      "\",\""      8     都道府県名                                       Japanese_CI_AS
8       SQLCHAR             0       40      "\",\""      9     市区町村名                                       Japanese_CI_AS
9       SQLCHAR             0       200     "\","      10     町域名                                           Japanese_CI_AS
10      SQLCHAR             0       1       ","      11    一町域が二以上の郵便番号で表される場合の表示     ""
11      SQLCHAR             0       1       ","      12    小字毎に番地が起番されている町域の表示           ""
12      SQLCHAR             0       1       ","      13    丁目を有する町域の場合の表示                     ""
13      SQLCHAR             0       1       ","      14    一つの郵便番号で二以上の町域を表す場合の表示     ""
14      SQLCHAR             0       5       ","      15    更新の表示                                       ""
15      SQLCHAR             0       5       "\r\n"   16    変更理由                                         ""
```

##### bcpでデータ投入

``` データ投入.bat
sqlcmd -S (local)\MSSQLSERVER -d COMMON_POSTCODE -U user -P pass -Q "truncate table dbo.WkTbl_共通_郵便番号_Original" -o "log_truncate_dbo.WkTbl_共通_郵便番号_Original.log"
sqlcmd -S (local)\MSSQLSERVER -d COMMON_POSTCODE -U user -P pass -Q "dbcc checkident ('dbo.WkTbl_共通_郵便番号_Original',RESEED,1)" -o "log_reseed_dbo.WkTbl_共通_郵便番号_Original.log"

bcp COMMON_POSTCODE.dbo.WkTbl_共通_郵便番号_Original in KEN_ALL.CSV -S (local)\MSSQLSERVER -U user -P pass -t, -q -f format_dbo.WkTbl_共通_郵便番号_Original.fmt
```

### データの内容の確認

では実際の内容を確認してみましょう。

Microsoft SQL Server Management Studio (以下、SSMS)で実行してみましょう。
次のスクリプトを実行してみます。

``` .sql
SELECT TOP (10) [uid]
      ,[全国地方公共団体コード]
      ,[旧郵便番号]
      ,[郵便番号]
      ,[都道府県カナ]
      ,[市区町村カナ]
      ,[町域カナ]
      ,[都道府県名]
      ,[市区町村名]
      ,[町域名]
      ,[一町域が二以上の郵便番号で表される場合の表示]
      ,[小字毎に番地が起番されている町域の表示]
      ,[丁目を有する町域の場合の表示]
      ,[一つの郵便番号で二以上の町域を表す場合の表示]
      ,[更新の表示]
      ,[変更理由]
  FROM [COMMON_POSTCODE].[dbo].[WkTbl_共通_郵便番号_Original]
```

実行した結果は、次のようになります。

``` 
uid	全国地方公共団体コード	旧郵便番号	郵便番号	都道府県カナ	市区町村カナ	町域カナ	都道府県名	市区町村名	町域名	一町域が二以上の郵便番号で表される場合の表示	小字毎に番地が起番されている町域の表示	丁目を有する町域の場合の表示	一つの郵便番号で二以上の町域を表す場合の表示	更新の表示	変更理由
1	01101	060  	0600000	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ	北海道	札幌市中央区	以下に掲載がない場合	0	0	0	0	0	0
2	01101	064  	0640941	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｱｻﾋｶﾞｵｶ	北海道	札幌市中央区	旭ケ丘	0	0	1	0	0	0
3	01101	060  	0600041	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾋｶﾞｼ	北海道	札幌市中央区	大通東	0	0	1	0	0	0
4	01101	060  	0600042	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾆｼ(1-19ﾁｮｳﾒ)	北海道	札幌市中央区	大通西（１～１９丁目）	1	0	1	0	0	0
5	01101	064  	0640820	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾆｼ(20-28ﾁｮｳﾒ)	北海道	札幌市中央区	大通西（２０～２８丁目）	1	0	1	0	0	0
6	01101	060  	0600031	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｷﾀ1ｼﾞｮｳﾋｶﾞｼ	北海道	札幌市中央区	北一条東	0	0	1	0	0	0
7	01101	060  	0600001	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｷﾀ1ｼﾞｮｳﾆｼ(1-19ﾁｮｳﾒ)	北海道	札幌市中央区	北一条西（１～１９丁目）	1	0	1	0	0	0
8	01101	064  	0640821	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｷﾀ1ｼﾞｮｳﾆｼ(20-28ﾁｮｳﾒ)	北海道	札幌市中央区	北一条西（２０～２８丁目）	1	0	1	0	0	0
9	01101	060  	0600032	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｷﾀ2ｼﾞｮｳﾋｶﾞｼ	北海道	札幌市中央区	北二条東	0	0	1	0	0	0
10	01101	060  	0600002	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｷﾀ2ｼﾞｮｳﾆｼ(1-19ﾁｮｳﾒ)	北海道	札幌市中央区	北二条西（１～１９丁目）	1	0	1	0	0	0
```

まず、uid=1の行に町域名が「"以下に掲載がない場合"」があります。

``` 
uid	全国地方公共団体コード	旧郵便番号	郵便番号	都道府県カナ	市区町村カナ	町域カナ	都道府県名	市区町村名	町域名	一町域が二以上の郵便番号で表される場合の表示	小字毎に番地が起番されている町域の表示	丁目を有する町域の場合の表示	一つの郵便番号で二以上の町域を表す場合の表示	更新の表示	変更理由
1	01101	060  	0600000	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ	北海道	札幌市中央区	以下に掲載がない場合	0	0	0	0	0	0
```

次に、uid=4の行に町域名に「"～"」が入っていて範囲を示す形になっています。

```
uid	全国地方公共団体コード	旧郵便番号	郵便番号	都道府県カナ	市区町村カナ	町域カナ	都道府県名	市区町村名	町域名	一町域が二以上の郵便番号で表される場合の表示	小字毎に番地が起番されている町域の表示	丁目を有する町域の場合の表示	一つの郵便番号で二以上の町域を表す場合の表示	更新の表示	変更理由
4	01101	060  	0600042	ﾎｯｶｲﾄﾞｳ	ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ	ｵｵﾄﾞｵﾘﾆｼ(1-19ﾁｮｳﾒ)	北海道	札幌市中央区	大通西（１～１９丁目）	1	0	1	0	0	0
```

問題となっている分割されている例を確認してみましょう。
「全角となっている町域部分の文字数が38文字を越える場合（省略）複数レコードに分割しています」とのことなので、町域名の文字数が大きいものを見てみます。
条件は38文字よりもへらして36文字以上とします。

``` .sql
select 
    PA.uid
    ,PA.郵便番号
    ,PA.市区町村名
    ,PA.町域名
    ,len(PA.町域名) as 町域名Len
from [COMMON_POSTCODE].[dbo].[WkTbl_共通_郵便番号_Original] PA
where len(PA.町域名)>=36
```

結果はこちらになります。

``` 
uid	郵便番号	市区町村名	町域名	町域名Len
6662	0580343	幌泉郡えりも町	東洋（油駒、南東洋、１３２～１５６、１５８～３５４、３６６、３６７番地）	36
8129	0882686	標津郡中標津町	俣落（１６２９－１、１６５３－３、１６８４－５、１８８６－２、１８９３、	36
8133	0882686	標津郡中標津町	２０２３－２、２０３８－２、２２５３－２、２２５３－５、２２５３－２１、	36
10193	0330071	上北郡六戸町	５、６３７、６４１、６４３、６４７を除く」、中屋敷、沼久保、根古橋、堀切	36
12764	0287914	九戸郡洋野町	種市第２２地割～第２３地割（一区、二区、三区、四区、大町、小橋、住吉町）	36
20022	9960301	最上郡大蔵村	１９２３－５を除く」、大谷地、折渡、鍵金野、金山、滝ノ沢、豊牧、沼の台、	36
21559	9620211	須賀川市	滝（赤土、雁田、北久保、白砂、白砂山、大新畑、大日前、滝西、チャクの下、	36
23126	9660401	耶麻郡北塩原村	桧原（雄子沢、雄子沢原、細野、南黄連沢、南黄連沢山、黄連沢山、黄連原山）	36
23127	9692701	耶麻郡北塩原村	桧原（秋元、荒砂沢山、裏磐梯、大府平、大府平原、小野川、小野川原、剣ケ峯、	37
23128	9692701	耶麻郡北塩原村	狐鷹森、五色沼、甚九郎沢山、甚九郎山、曽原山、寺沢山、蛇平原山、湯平山、	36
39957	1506290	渋谷区	桜丘町渋谷サクラステージＳＨＩＢＵＹＡサイドＳＨＩＢＵＹＡタワー（地階・	36
39959	1506207	渋谷区	桜丘町渋谷サクラステージＳＨＩＢＵＹＡサイドＳＨＩＢＵＹＡタワー（７階）	36
（省略）
39990	1506238	渋谷区	桜丘町渋谷サクラステージＳＨＩＢＵＹＡサイドＳＨＩＢＵＹＡタワー（３８階）	37
39991	1506239	渋谷区	桜丘町渋谷サクラステージＳＨＩＢＵＹＡサイドＳＨＩＢＵＹＡタワー（３９階）	37
52365	9218046	金沢市	ク、ケ、御所谷、小寺山、シ、下上野、下西欠、平、チ、ツ乙、ツ丙、テ、ト、	36
57525	4070311	北杜市	大泉町西井出８２４０－１（美森、たかね荘、清泉寮、サンメドウズスキー場）	36
59023	3994232	駒ヶ根市	下平（２０７０～２１１９、２３０９～２３１１、２４１９、２４２１～２４２６	37
59393	3842304	北佐久郡立科町	茂田井（１～５００「２１１番地を除く」「古町」、２５２７～２５２９「土遠」）	38
66638	4530002	名古屋市中村区	名駅（１－１－８、１－１－１２、１－１－１３、１－１－１４、１－３－４、	36
78536	6028064	京都市上京区	一町目（上長者町通堀川東入、東堀川通上長者町上る、東堀川通中立売通下る）	36
78626	6028062	京都市上京区	中長者町上る、油小路通中長者町下る、上長者町通油小路西入、上長者町通油小	36
78631	6028119	京都市上京区	川西入、下長者町通葭屋町東入、下長者町通葭屋町西入、出水通葭屋町西入、出	36
78645	6028244	京都市上京区	通葭屋町西入、上長者町通葭屋町東入、下長者町通葭屋町西入、下長者町通葭屋	36
78663	6028368	京都市上京区	の下立売通御前西入上る、上の下立売通御前西入２丁目、上の下立売通御前西入	36
78664	6028368	京都市上京区	２筋目、下長者町通御前西入、天神道上の下立売上る、天神道仁和寺街道下る、	36
78666	6028368	京都市上京区	道天神道西入下る、仁和寺街道天神道東入下る、御前通上の下立売上る、御前通	36
78668	6028368	京都市上京区	和寺街道下る西入、御前通妙心寺道上る西入、御前通西裏上の下立売上る、御前	36
78671	6028392	京都市上京区	丁目、御前通今出川上る２丁目、御前通今出川上る２丁目西入、御前通今出川上	36
78672	6028392	京都市上京区	る２筋目、御前通今出川上る西入、御前通今出川上る東入、御前通今出川２丁目	36
78811	6028256	京都市上京区	神明町（上長者町通松屋町東入、松屋町通上長者町上る、松屋町通中立売下る）	36
78911	6020093	京都市上京区	上る、大宮通寺之内上る、大宮通寺之内上る２丁目、上御霊前通大宮西入、上御	36
78914	6028362	京都市上京区	通上の下立売上る２丁目、御前通下立売上る、御前通下立売上る２丁目、御前通	36
78915	6028362	京都市上京区	下立売上る２丁目西入、御前通下立売上る東入、下ノ森通下立売上る、下ノ森通	36
78945	6028414	京都市上京区	る東入、大宮通寺之内半丁下る東入、大宮通寺之内下る東入１丁目、寺之内通大	36
78973	6020956	京都市上京区	西町（東堀川通元誓願寺下る、元誓願寺通油小路西入、元誓願寺通東堀川東入）	36
79039	6020816	京都市上京区	町西入下る、上立売通寺町西入４筋目下る、寺町通今出川上る１筋目西入、寺町	36
79042	6020816	京都市上京区	る４丁目西入、寺町通今出川西入、寺町通今出川２丁目上る西入、塔之段寺町通	36
79045	6028325	京都市上京区	今出川上る、七本松通今出川下る、七本松通元誓願寺上る、七本松通五辻下る）	36
79153	6028045	京都市上京区	下る、出水通東堀川東入、下立売通堀川東入、下立売通堀川東入上る、下立売通	36
79767	6048345	京都市中京区	鍛冶屋町（六角通猪熊西入、六角通黒門東入、六角通黒門西入、六角通大宮東入）	37
79789	6040811	京都市中京区	亀屋町（堺町通夷川下る、堺町通二条上る、夷川通堺町西入、夷川通堺町東入）	36
79837	6040983	京都市中京区	町通麩屋町東入、竹屋町通御幸町西入、夷川通麩屋町西入、夷川通麩屋町東入）	36
79858	6048247	京都市中京区	塩屋町（三条通西洞院西入、三条通小川東入、三条通小川西入、三条通油小路東入）	38
79911	6040014	京都市中京区	大黒町（釜座通夷川下る、釜座通二条上る、夷川通釜座東入、夷川通釜座西入）	36
79915	6048073	京都市中京区	六角上る、富小路通六角下る、六角通富小路西入、六角通富小路東入、六角通柳	36
79918	6040071	京都市中京区	大文字町（丸太町通堀川東入、丸太町通油小路西入、丸太町通油小路東入、丸太	36
79926	6040835	京都市中京区	高宮町（御池通高倉西入、御池通東洞院東入、御池通間之町東入、御池通間之町	36
80194	6040805	京都市中京区	百足屋町（夷川通柳馬場西入、夷川通堺町東入、夷川通堺町西入、夷川通高倉東入）	38
80623	6008146	京都市下京区	る、河原町通七条下る、七条通間之町西入、七条通間之町東入、七条通河原町西	36
80711	6008468	京都市下京区	入、堀川通新花屋町上る、堀川通新花屋町下る、堀川通花屋町上る、堀川通六条	36
80722	6008128	京都市下京区	下る、河原町通六条下る、土手町通上ノ口下る、土手町通正面上る、上枳殻馬場	36
80757	6008157	京都市下京区	橘町（下珠数屋町通不明門東入、下珠数屋町通烏丸東入、下珠数屋町通東洞院西入）	38
80909	6008185	京都市下京区	上珠数屋町通東洞院東入、東洞院通上枳殻馬場下る、間之町通上珠数屋町下る）	36
80960	6008425	京都市下京区	骨屋町（諏訪町通高辻上る、諏訪町通高辻下る、高辻通烏丸西入、高辻通室町東入）	38
81038	6008314	京都市下京区	新花屋町上る、若宮通新花屋町下る、若宮通花屋町上る、若宮通花屋町下る、若	36
86063	5900158	堺市南区	７２６－１、７２７、７２８、７２８－１、７２９、７３０、７３１、７３２、	36
89278	6511102	神戸市北区	山田町下谷上（大上谷、修法ケ原、中一里山「９番地の４、１２番地を除く」、	36
90824	6780141	相生市	相生（５１３３番地２～５１８７番地、５３１６番地１～５３１６番地１６７）	36
107011	7770301	美馬市	木屋平（市初、今丸、尾山、カゴミ、樫原、木中、桑柄、ケヤキヒラ、小日浦、	36
107014	7770303	美馬市	木屋平（川上、川上カケ、太合、太合カケ、谷口、谷口カケ、森遠、八幡、弓道）	37
107015	7712107	美馬市	美馬町（雨下、上野、小原、境目、里西屋敷、下ノ段、城、滝下、竹ノ内、田辺、	37
107020	7712106	美馬市	喜来市、里平野、下突出、谷ヨリ西、乳ノ木、寺ノ下、天神、天神北、中東原、	36
107023	7712102	美馬市	川ノ上、観音、栗林、黒砂、狙ケ内、猿坂、下白地、白地、炭釜、芹佐古、惣後、	37
107024	7712102	美馬市	惣田、外墓、滝ノ上、立見山、田ノ岡、狙坂、中岡、中野田ノ井、中横尾、長地、	37
107034	7712104	美馬市	美馬町（鵜飼口、岡、小長谷、小長谷端、笠仏、川縁、荒神、助松、高畑、辰角、	37
107602	7794104	美馬郡つるぎ町	貞光（太田西、太田東、太田東山、小長谷、小山北、柴内、柴山、僧地、僧山）	36
113362	8260043	田川市	奈良（青葉町、大浦、会社町、霞ケ丘、後藤寺西団地、後藤寺東団地、希望ケ丘、	37
122524	8911275	鹿児島市	川上町（３６４９、３６６１、３６６７、３６６７－３、３６６９－４、３６７１、	38
122525	8911275	鹿児島市	３６７２、３６７３－１、３６７４、３６７４－２、３６７４－８、３６８０－１、	38
122527	8911275	鹿児島市	４１２８－３、４１２８－４、４１２８－５、４１３２、４１３２－４、４１３３、	38
122528	8911275	鹿児島市	４１３３－２、４１３８、４２０３、４２０３－１、４２０９、４２０９－６、	36
122531	8911275	鹿児島市	４２４１、４２４２、４２４２－３、４２４４、４２４４－１、４２４４－３、	36
```

uid=8129の行ですが、町域名=「"俣落（１６２９－１、１６５３－３、１６８４－５、１８８６－２、１８９３、"」で文字数は36文字となっています。
しかし、どう見ても括弧が半端なので複数レコードに分割されていますね。
なので「文字数が38文字なら分割したレコード」との判定はできません。

### 結合の規則の推定

「既定の文字数になった行は結合する」との規則では結合できないので、別の規則が必要です。
先ほどの
```
俣落（１６２９－１、１６５３－３、１６８４－５、１８８６－２、１８９３、
```
をみると括弧が対応していることを根拠にすることは可能に思えます。
その前に、どのような文字が使われているのか調べてみましょう。

まず、次のように関数を定義します。
``` WkFnTb_Common_ParseString.sql

create function [dbo].[WkFnTb_Common_ParseString](
	@str nvarchar(2000)
)
returns @Ret table(
	LetterIndex int
	,OneLetter nvarchar(1)
)
as
begin
	
declare @i int;
declare @max int=len(isnull(@str,''));

set @i=1;
while @i<=@max
begin
	insert into @Ret
	select
		@i as LetterIndex
		,SUBSTRING(@str,@i,1) as OneLetter
	;
	set @i=@i+1;
end;

return;

end

```

この関数は、文字列を一レコードづつに展開するものです。
この関数を使って、使われている文字数をカウントします。

``` 町域名の文字カウント.sql
select 
	OneLetter
	,count(*) as CountOfLetter
from 
(
select 
	FNParse.OneLetter
from [COMMON_POSTCODE].[dbo].[WkTbl_共通_郵便番号_Original] A
cross apply dbo.[WkFnTb_Common_ParseString](A.町域名) FNParse
) PA
group by PA.OneLetter
order by  PA.OneLetter

```

結果はこのようになりました。

```
OneLetter	CountOfLetter
－	205
（	6555
）	6555
、	2240
～	349
「	26
」	26
〔	1
〕	1
○	2
・	187
０	706
１	2274
２	1981
...
（省略）

```

文字の種類とデータでの使われ方から用途は次のものと推測されます。
「"－"」(205文字)　 地番の枝番を表す。
「"（"」(6555文字)、「"）"」(6555文字)　町域名の字名などを細分化する条件の内容を表す。
「"「"」(26文字)、「"」"」(26文字)　町域名の字名などを細分化する条件の内容を表す。
「"、"」(2240文字)　町域名のなかで列挙する場合に区切りを表す。
「"・"」(187文字)　町域名のなかで列挙する場合に区切りを表す。
「"～"」(349文字)　地番などの範囲を表す。
「"〔"」(1文字)、「"〕"」(1文字)　「"〔東京電力福島第二原子力発電所構内〕"」の用例があり、建物の意味か省略可能の意味と思われる。
「"○"」(2文字)　「"富岡（○○屋敷）"」の用例があり、複数の文字に対応するものと判断される。

文字の用途から、結合規則はつぎのようにします。

1. 町域名のなかにある「"（"」と「"）"」および「"「"」と「"」"」は必ずペアになっている。
1. もし、ペアとして成立しない場合はuidが+1となるレコードの町域名を後ろに付け加えてペアになるかを判定する。ペアが成立するまで繰り返す。


 ### レコードの結合

 前述の結合規則で結合してみましょう。

 まず、括弧のペアの判定をする関数を定義します。
 
 ``` WkFnTb_Common_括弧階層.sql
create function [dbo].[WkFnTb_Common_括弧階層](
	@str nvarchar(2000)
)
returns @Ret table(
	括弧階層 int
	,鉤括弧階層 int
)
as
begin
	
declare @i int;
declare @max int=len(isnull(@str,''));
declare @括弧階層 int=0;
declare @鉤括弧階層 int=0;

set @i=1;
while @i<=@max
begin
	declare @work nvarchar(1)=SUBSTRING(@str,@i,1);
	set @括弧階層=case @work 
		when '（' then 1 
		when '）' then -1 
		else 0 end
	+@括弧階層;
	set @鉤括弧階層=case @work 
		when '「' then 1 
		when '」' then -1 
		else 0 end
	+@鉤括弧階層;
	set @i=@i+1;
end;

insert into @Ret
(括弧階層,鉤括弧階層) 
values 
(@括弧階層,@鉤括弧階層);

return;

end

 ```

 結合結果を保存するテーブルを定義します。

 ``` WkTbl_共通_郵便番号_町域結合.sql
 CREATE TABLE [dbo].[WkTbl_共通_郵便番号_町域結合](
	[uid] [int] NOT NULL,
	[全国地方公共団体コード] [nvarchar](10) NOT NULL,
	[郵便番号] [nvarchar](15) NOT NULL,
	[都道府県カナ] [nvarchar](15) NOT NULL,
	[市区町村カナ] [nvarchar](30) NOT NULL,
	[町域カナ] [nvarchar](max) NOT NULL,
	[都道府県名] [nvarchar](10) NOT NULL,
	[市区町村名] [nvarchar](20) NOT NULL,
	[町域名] [nvarchar](max) NOT NULL,
	[小字毎に番地が起番されている町域の表示] [bit] NOT NULL,
	[丁目を有する町域の場合の表示] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

```

念のために結合前と結合後の関係を保存するテーブルを作成します。

``` WkTbl_内部_対応表_Originalから町域結合.sql
CREATE TABLE [dbo].[WkTbl_内部_対応表_Originalから町域結合](
	[uid_郵便番号_Original] [int] NOT NULL,
	[uid_郵便番号_町域結合] [int] NOT NULL,
	[結合ID_町域結合] [int] NULL
) ON [PRIMARY]

```

結合を行うストアドはつぎのようになります。

``` WkPr_共通_郵便番号TBL町域結合.sql
create procedure [dbo].[WkPr_共通_郵便番号TBL町域結合]
as 
begin

-- 作業用テーブル
declare @階層TBL as table
(
	uid int not null primary key
	,括弧階層増分 int not null
	,鉤括弧階層増分 int not null
	,括弧階層累積 int not null default((0))
	,鉤括弧階層累積 int not null default((0))
	,結合ID int default((0))
	,結合完了FLG bit not null default((0))
	,町域カナ結合 nvarchar(max) not null
	,町域結合 nvarchar(max) not null
);

insert into @階層TBL
(
	uid
	,括弧階層増分
	,鉤括弧階層増分
	,町域カナ結合
	,町域結合
)
SELECT 
	A.[uid]
	,FN階層.括弧階層 as 括弧階層増分
	,FN階層.鉤括弧階層 as 鉤括弧階層増分
	,A.[町域カナ] as 町域カナ結合
	,A.[町域名] as 町域結合
FROM [dbo].WkTbl_共通_郵便番号_Original A
cross apply dbo.WkFnTb_Common_括弧階層(A.町域名) FN階層
;

declare 
	@括弧階層増分　int
	,@鉤括弧階層増分 int
	,@括弧階層累積　int=0
	,@鉤括弧階層累積 int=0
	,@結合ID int=0
	,@結合ID_identity int=0
	,@結合完了FLG bit
	,@町域カナ nvarchar(max)
	,@町域 nvarchar(max)
	,@町域カナ結合 nvarchar(max)=''
	,@町域結合 nvarchar(max)=''
;

declare @i int=1;
declare @max int=(select max(uid) from @階層TBL);

while @i<=@max
begin

select 
	@括弧階層増分=TBL.括弧階層増分
	,@鉤括弧階層増分=TBL.鉤括弧階層増分
	,@町域カナ=TBL.町域カナ結合
	,@町域=TBL.町域結合
from @階層TBL TBL
where TBl.uid=@i;

set @括弧階層累積=@括弧階層累積+@括弧階層増分;
set @鉤括弧階層累積=@鉤括弧階層累積+@鉤括弧階層増分;

if @町域カナ結合!=@町域カナ
	set @町域カナ結合=@町域カナ結合+@町域カナ
;
set @町域結合=@町域結合+@町域;

if @括弧階層累積=0 and @鉤括弧階層累積=0 
	set @結合完了FLG=1
else
	set @結合完了FLG=0
;

if @結合ID=0 and @結合完了FLG=0
-- 前行は結合行ではなく、今行は結合行であり結合行の先頭にあたる。
begin
	set @結合ID_identity=@結合ID_identity+1;
	set @結合ID=@結合ID_identity;
end;

update A set
	括弧階層累積=@括弧階層累積
	,鉤括弧階層累積=@鉤括弧階層累積
	,結合ID=@結合ID
	,結合完了FLG=@結合完了FLG
	,町域カナ結合=@町域カナ結合
	,町域結合=@町域結合
from @階層TBL A
where A.uid=@i;

if @結合完了FLG=1
begin
	set @町域カナ結合='';
	set @町域結合='';
	set @結合ID=0;
end
;

set @i=@i+1;

end -- of while
;

truncate table dbo.[WkTbl_共通_郵便番号_町域結合];
dbcc checkident('dbo.[WkTbl_共通_郵便番号_町域結合]', RESEED, 1);

INSERT INTO [dbo].[WkTbl_共通_郵便番号_町域結合]
(
	[uid]
	,[全国地方公共団体コード]
	,[郵便番号]
	,[都道府県カナ]
	,[市区町村カナ]
	,[町域カナ]
	,[都道府県名]
	,[市区町村名]
	,[町域名]
	,[小字毎に番地が起番されている町域の表示]
	,[丁目を有する町域の場合の表示]
)
select 
	A.uid
	,POST.全国地方公共団体コード
	,POST.郵便番号
	,POST.都道府県カナ
	,POST.市区町村カナ
	,A.町域カナ結合 as 町域カナ
	,POST.都道府県名
	,POST.市区町村名
	,A.町域結合 as 町域名
	,POST.小字毎に番地が起番されている町域の表示
	,POST.丁目を有する町域の場合の表示
from @階層TBL A
left outer join dbo.WkTbl_共通_郵便番号_Original POST
on A.uid=POST.uid 
where 
A.結合完了FLG=1
order by A.uid
;

truncate table dbo.WkTbl_内部_対応表_Originalから町域結合;

insert into dbo.WkTbl_内部_対応表_Originalから町域結合
(
	uid_郵便番号_Original
	,uid_郵便番号_町域結合
	,結合ID_町域結合
)
select 
	A.uid as uid_郵便番号_Original
	,isnull(B.uid,A.uid) as uid_郵便番号_町域結合
	,B.結合ID as 結合ID_町域結合
from @階層TBL A
left outer join
(
select 
	SB.uid
	,SB.結合ID
from @階層TBL SB
where SB.結合ID!=0 and SB.結合完了FLG=1
) B
on A.結合ID=B.結合ID
;

end

```

ストアドを実行すると暫くかかりますので注意してください。
私のPCでは１分半ほどかかりました。

### 結合されたものを見てみよう

例えば町域名の文字数が200を超えるものを見てみましょう。

``` .sql

select 
	A.uid
	,A.郵便番号
	,A.町域カナ
	,A.町域名
	,len(A.町域カナ) as 町域カナLen
	,len(A.町域名) as 町域名Len
from dbo.WkTbl_共通_郵便番号_町域結合 A
where len(A.町域名)>200
order by A.uid

```

結果は次のとおりです。

```
uid	郵便番号	町域カナ	町域名	町域カナLen	町域名Len
78669	6028368	ｷﾀﾏﾁ	北町（上の下立売通天神道西入上る、上の下立売通御前西入、上の下立売通御前西入上る、上の下立売通御前西入２丁目、上の下立売通御前西入２筋目、下長者町通御前西入、天神道上の下立売上る、天神道仁和寺街道下る、天神道下立売上る、天神道妙心寺道上る、天神道妙心寺道上る西入、仁和寺街道天神道西入下る、仁和寺街道天神道東入下る、御前通上の下立売上る、御前通上の下立売上る西入、御前通下立売上る、御前通下長者町上る西入、御前通仁和寺街道下る西入、御前通妙心寺道上る西入、御前通西裏上の下立売上る、御前通西裏下立売上る）	4	252
78972	6028374	ﾆｼﾏﾁ	西町（一条通御前西入、一条通紙屋川東入、一条通天神道西入、一条通御前３丁目西入、一条通御前西入上る、一条通御前西入上る西入、一条通御前西入２丁目、一条通御前西入２丁目上る、一条通御前西入２丁目下る、一条通御前西入２筋目下る、一条通御前西入３丁目、一条通御前西入３丁目上る、一条通御前西入３丁目下る、一条通御前西入３筋目、一条通御前西入下る、一条通御前西入下る３丁目、一条通紙屋川東入上る、一条通紙屋川東入下る、天神道一条上る、天神道一条下る、天神道仁和寺街道上る）	4	232
94157	6308037	ﾅｶﾏﾁ(5115-5149､5171､5183､5186､5192-5196､5198-5212､5227-5229､5239-5257､5263､5269-5271､5275､5276-4､5276-5､5277-5280､5284-5312､5314､5316-5327､5346､5349､5351-5352､5355-5362､5364-5424､5426､5429-5430､5445､5455､5461､5463､5470､5472､5475､5482-5529)	中町（５１１５～５１４９、５１７１、５１８３、５１８６、５１９２～５１９６、５１９８～５２１２、５２２７～５２２９、５２３９～５２５７、５２６３、５２６９～５２７１、５２７５、５２７６－４、５２７６－５、５２７７～５２８０、５２８４～５３１２、５３１４、５３１６～５３２７、５３４６、５３４９、５３５１～５３５２、５３５５～５３６２、５３６４～５４２４、５４２６、５４２９～５４３０、５４４５、５４５５、５４６１、５４６３、５４７０、５４７２、５４７５、５４８２～５５２９）	239	237
122532	8911275	ｶﾜｶﾐﾁｮｳ(3649､3661､3667､3667-3､3669-4､3671､3672､3673-1､3674､3674-2､3674-8､3680-1､3701､3704､3723-3､3723-5､4125､4128､4128-3､4128-4､4128-5､4132､4132-4､4133､4133-2､4138､4203､4203-1､4209､4209-6､4211-1､4215､4215-1､4216-3､4216-10､4216-12､4236-1､4238､4238-1､4238-2､4241､4242､4242-3､4244､4244-1､4244-3､4244-4ﾊﾞﾝﾁ)	川上町（３６４９、３６６１、３６６７、３６６７－３、３６６９－４、３６７１、３６７２、３６７３－１、３６７４、３６７４－２、３６７４－８、３６８０－１、３７０１、３７０４、３７２３－３、３７２３－５、４１２５、４１２８、４１２８－３、４１２８－４、４１２８－５、４１３２、４１３２－４、４１３３、４１３３－２、４１３８、４２０３、４２０３－１、４２０９、４２０９－６、４２１１－１、４２１５、４２１５－１、４２１６－３、４２１６－１０、４２１６－１２、４２３６－１、４２３８、４２３８－１、４２３８－２、４２４１、４２４２、４２４２－３、４２４４、４２４４－１、４２４４－３、４２４４－４番地）	303	297
```

## つづく

今回はデータがSQLServerのテーブルに読み込み、分割された行を結合するところまで行いました。

いまのところ、次をどうすればいいかが見通せていません。
次回の記事はしばらく先になるかもしれません。