（追記 2025/04/06）
つづき
前回の内容は
- 郵便番号データにはデータベース的に利用するには問題がある。
- この記事では問題を解決する経緯を見せるもので、解決方法がないこともありうる。
まずはデータを登録しよう
議論を具体的に進めるために、郵便番号データをSQLServerに登録します。
郵便番号データの取得と展開
郵便番号データは、以下のリンクをクリックするとダウンロードできます。
郵便番号データ（SJIS、全国一括、2025年3月時点 1,694,353Byte）
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文字) 「"富岡（○○屋敷）"」の用例があり、複数の文字に対応するものと判断される。
文字の用途から、結合規則はつぎのようにします。
- 町域名のなかにある「"（"」と「"）"」および「"「"」と「"」"」は必ずペアになっている。
- もし、ペアとして成立しない場合は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のテーブルに読み込み、分割された行を結合するところまで行いました。
いまのところ、次をどうすればいいかが見通せていません。
次回の記事はしばらく先になるかもしれません。