C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[5]: SQLServerでの漢数字→半角数字変換

分類:[その他の言語]　

DBがSQL-server(2016)で住所情報を扱うシステムの改修を担当してます。

～マスタにSSISでDBにCSV、TSVから
(TSVに関しては本当はExcelですが、Excel用OLEDBプロバイダーがインストールされないためSSISの機能やT-SQLでExcelから直接Insertの手段が取れません)
bulk insert or 一括挿入タスクでマスタにインポートするフローを実装中です。

その際、ファイルの提供元により住所情報の丁目等の数字が、
全角数字だったり、漢数字だったりバラバラなので、マスタインポートのプロジェクト(パッケージ)内で半角数値に統一する処理が必要です。
後続処理の名寄せを丁目(大字)単位までで行う必要があるのと対照テーブルの既存レコードが半角数字で統一させる必要があるため、
要件的に必須の機能です。
イメージ的にはSSIS上、一旦、マスタにそのまま入れ、次のフローで一括Updateするイメージで実装しようと考えています。

全角数値については、あまりスマートとは言えませんが、
SQL-ServerのReplace関数が正規表現に対応していないようなので、
スカラ値関数で、
Whileで、文字コード対比で10回ループして、Replaceをかける関数をつくり、
SQLでスカラ値関数を呼ぶ方法でステップ数少なめにできる見込みですが、
漢数字の方が文字コードの順番が半角数字と異なるため、loopでステップ数を少なくできる見込みがありません。

以下のいずれの方法が適当でしょうか？

(1)横着せず、t-SQLでreplaceを10行書く
(2)SSISでカーソルに取得して、式タスクで何とかする
(3)スクリプトタスクでVB or C# + ADO.netでloopしながらstrconvで変換する
(4)その他：具体的にご教示いただければありがたく

制約条件としては、
保守フェーズ以降に私が関われる可能性が低く、
来年度以降の面倒を見るメンバーに、VBとかC#のスキルがありません。
したがって、できればSQLかT-SQLで何とかした方が都合が良いです。

よろしくお願いします。

■No86754 (大谷刑部さん) に返信

CLR関数を使ってみるのはどうでしょう？
CLR関数自体はVBとかC#で作成することになりますが
作ってしまえばあとの呼び出しは通常のSQL文での
記述とほとんど変わらず記述できまるのでなんとかなりませんでしょうか？

■No86754 (大谷刑部さん) に返信

どういう条件で、どこまでやるかによって話がかなり違ってくると思うのですが？

多分、単純に「一二三・・・」を「123・・・」に変換すれば済むというような話で
はないと想像していますが・・・

どこまで対応するかによっては、結構悩ましい問題のようです。

漢数字を数字に直す処理
https://teratail.com/questions/66834

回答ではなくてすみません。

■No86755 (shu さん) に返信
> ■No86754 (大谷刑部さん) に返信
>
> CLR関数を使ってみるのはどうでしょう？
> CLR関数自体はVBとかC#で作成することになりますが
> 作ってしまえばあとの呼び出しは通常のSQL文での
> 記述とほとんど変わらず記述できまるのでなんとかなりませんでしょうか？

SQL文のロジックのシンプルさから言えばその通りなのですが、

>>制約条件としては、
>>保守フェーズ以降に私が関われる可能性が低く、
>>来年度以降の面倒を見るメンバーに、VBとかC#のスキルがありません。
のところに引っかかります。
要するに、VBやC#の構文が理解できてない人に.Netの概念自体を理解させるのが難しいため
(次年度以降の面倒を見る人がそもそもプロのSEではありません。監査法人系のコンサルです)
有力な実装候補にはなり得ません。

せいぜいVBのソースが直接参照できる、スクリプトタスクのVBの実装なら理解できるかもという感じです。

ボクも直接の回答ではなくスミマセン。

例えば、「3丁目16番20号」を略して書くと、「3-16-20」のような表記になりますが、
元データには厳密な記載と略記が混在していると思います。
「三丁目一六番二〇号」のようなデータの場合、
「3丁目16番20号」でOKなのか、「3-16-20」まで変換するのか、
どちらなんでしょう？
※恐らく、後者までやるとなると、その土地独特の表現があったりして
　色々と大変かも知れません。

あと、「三丁目十六番二十号」のようなバリエーションにも対応が必要ですし、
番地を表す番号が3桁以上の場合（有り得る）を考えただけでゾッとします。
いずれにしろ、10回Replaceで対応できるような代物ではないです。

あと、関係ないかも知れませんが、
例えば、町名等に漢数字が含まれる場合（○○県△△市七日町一丁目二番三号）、
丁目・番地との区別は出来ているという前提でよいのですよね？

同じく直接の回答ではありません。

どういうパターンがあるか興味があったので軽くググってみた所、
以下の住所正規化の製品ページが見つかりました。

http://biz.kkc.co.jp/software/geo/addressmatch/function/

このページにある通り、一筋縄で行くような話ではないですね。。。
挫けずに頑張るのであれば、上記ページに色々なパターンが書かれていますので
参考になるかと思います。

■No86759 (大谷刑部さん) に返信

Excelでやればいんじゃね

あと、こんな回答だと怒られるかもしれませんが、
筆まめのようなソフトでの一括変換を利用するのが賢いかも知れません。
CSV等の外部ファイルのインポート・エクスポートもできますし、
変換結果を出力したものでBULK INSERTするとか。
完全自動化が目的ではなく、プロのSE・PGが介入できないのであれば、
こういった方法も検討が必要と思います。

■No86773 (woow さん) に返信
> あと、こんな回答だと怒られるかもしれませんが、
> 筆まめのようなソフトでの一括変換を利用するのが賢いかも知れません。
> CSV等の外部ファイルのインポート・エクスポートもできますし、
> 変換結果を出力したものでBULK INSERTするとか。
> 完全自動化が目的ではなく、プロのSE・PGが介入できないのであれば、
> こういった方法も検討が必要と思います。

別のお客さんでこの解決法を取っているところはありますね。
今回は予算の関係で追加のライセンス費用発生が無理なのでこの方法はなしです。

確かに皆さんのおっしゃる通り、汎用性のあるロジックを組もうとすると、
千くらいまでの対応が必要になり、どの技術で組もうが条件分岐がとてつもなく複雑になるので、
結論から言えば、十までの対応にしました。

今回の要件から言えば、インポート後のデータが大字迄で名寄せできれば良いので、
丁目の最大が40代であることを根拠に、大字ー字タイプの住所はイレギュラデータ扱いとし、
お客さん（エンドユーザー）に了解を取って十までの対応でスカラ関数で実装し、
インポートタスクの次にSQLタスクでupdateべた書きでスカラ関数戻り値で大字以降項目を更新の方針としました。

今現在はインポート本体フローの一括挿入タスクでドはまりしてます。
CSVの項目(複数ファイル想定)とテーブル項目項目数が異なるため、（更新日時、削除フラグ等）
所定のフォルダに配置したファイルに足りない項目数のカンマをスクリプトタスク（VB）で足し、
そのフローで、一括挿入タスクを実行してますが、
VBで容易に認識できているパスが認識できておらず異常終了します。
（ファイルが開けない、パスがいない的なメッセージが出てます）
改行コード、開始行の設定が問題ないことは確認してます。
ひょっとして、日本語デレクトリ名だとダメとか'でくくらないとだめとかでしょうか？

ついでで申し訳ございませんが、わかる方いれば、
ご教示いただければ助かります。

よろしくお願いします。

C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[5]: SQLServerでの漢数字→半角数字変換

過去ログには書き込み不可