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

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

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

Re[10]: SQLであいまいな文字の検索範囲を広げたい

(過去ログ 39 を表示中)

[トピック内 15 記事 (1 - 15 表示)]　 << 0 >>

■20045 / inTopicNo.1)

　SQLであいまいな文字の検索範囲を広げたい

□投稿者/ はまや (18回)-(2008/06/04(Wed) 20:01:53)

分類:[データベース全般]　

お世話になります

初めてのC#、初めて触るADO.NETで、初めてデータベースアプリケーションを
作り始めて早２ヶ月。最近は得たい結果を得れるようにまで成長しましたが
こうなってくると、「このやり方は正しいのか？」とか「もっと効率の良い
方法があるのでは？」という疑問ばかり浮かんできます。
しかも、それはとても簡単な事だったりします（難しければ最初の方法で満足してしまうので）

そこで、質問なんですが
データベースに電話番号の項目があります。この項目の入力フォーマットは自由です
（(0000)11-2222とか0000-11-2222とか0000112222など）
しかし検索するときは、数字のみです。
なので↓こういうSQL文を作ってみました。
SELECT tel FROM dbo.customer WHERE REPLACE(REPLACE(REPLACE(tel, '-', ''), '(', ''), ')', '') LIKE '%1122%'

検索前に括弧とハイフンを消しています。
得たい結果は得られているのですが、どうもすっきりしません。

この方法について
「それしかないので仕方ない」とか「もっと良い方法がある」（出来ればその方法も）という
意見をよろしくお願いします。

ちなみに、「そもそも、入力のフォーマットを統一しろ」とか「REPLACE部分をストアドファンクション化する」
というのは無しの方向でお願いします。

開発環境
WindowsXP(SP2)
VS2005 C#（ADO.NET）
SQL Server 2005 Developer

引用返信 編集キー/

■20046 / inTopicNo.2)

　Re[1]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ やじゅ (434回)-(2008/06/04(Wed) 20:23:36)

2008/06/04(Wed) 20:26:37 編集(投稿者)

■No20045 (はまやさん) に返信
> データベースに電話番号の項目があります。この項目の入力フォーマットは自由です
> （(0000)11-2222とか0000-11-2222とか0000112222など）
> しかし検索するときは、数字のみです。
> なので↓こういうSQL文を作ってみました。
> SELECT tel FROM dbo.customer WHERE REPLACE(REPLACE(REPLACE(tel, '-', ''), '(', ''), ')', '') LIKE '%1122%'
>
> VS2005 C#（ADO.NET）
> SQL Server 2005 Developer
>

SQL Server 2005なら、正規表現が使えます。
http://msdn.microsoft.com/ja-jp/library/ms174214.aspx

引用返信 編集キー/

■20047 / inTopicNo.3)

　Re[1]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ trapemiya (1回)-(2008/06/04(Wed) 20:41:34)

私がよくやるのは、検索専用の列を一つ作り、データを保存する際にそこに数値のみにした値を放り込む方法です。これならインデックスも張れるので高速な検索が期待できます。
ご提示されているReplaceを使う方法は、データ件数が増えてくると検索速度が落ちるでしょう。

検索専用の列を作ることができない場合は、私もReplaceを使うぐらいしか思いつきません。

引用返信 編集キー/

■20051 / inTopicNo.4)

　Re[2]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ やじゅ (435回)-(2008/06/04(Wed) 22:28:42)

■No20047 (trapemiya さん) に返信
> 私がよくやるのは、検索専用の列を一つ作り、データを保存する際にそこに数値のみにした値を放り込む方法です。
> これならインデックスも張れるので高速な検索が期待できます。

補足として
前方一致　　Like '1122%' 　インデックスを使ってくれます。
後方一致　 Like '%1122' インデックスを使ってくれません。
前後方一致　Like '%1122%' インデックスを使ってくれません。
http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink9

引用返信 編集キー/

■20058 / inTopicNo.5)

　Re[3]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ はつね (769回)-(2008/06/04(Wed) 23:53:29)

2008/06/05(Thu) 00:14:12 編集(投稿者)

■No20051 (やじゅさん) に返信
> 補足として
> 前方一致　　Like '1122%' 　インデックスを使ってくれます。
> 後方一致　 Like '%1122' インデックスを使ってくれません。
> 前後方一致　Like '%1122%' インデックスを使ってくれません。
> http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink9

意外だったので追試していました。
確かに、引用されているURLのページが前提としているOracleの場合は、
前方一致のみインデックス、後方一致、前後方一致でインデックスを使
わないようです。
# PKだと前方一致すらインデックスが使われない

しかし、スレ主さんが使われているSQL Server 2005の場合で試してみると、
前方一致、後方一致、前後方一致でClustered Index Scanが行われます。

引用返信 編集キー/

■20061 / inTopicNo.6)

　Re[4]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ ネタ好き (371回)-(2008/06/05(Thu) 01:29:36)

2008/06/05(Thu) 01:30:23 編集(投稿者)

オプテマイザの性能に左右されるから、DBでは基本的には前方一致しかインデックスが使われないと思ったほうがいいでしょう。因みにインデックスをつけている列とつけていない列の混合も色々ありますので、常にチェックしておいた方がいいです。例えば、ある列にインデックスをつけていても、他の列と混合して検索条件にすると【インデックス】を使ってくれない場合もあります。また、FROM句の順番、WHERE句の列の指定順も注意が必要です。データマッチングの入れ後アルゴリズムに影響を与えます。
とはいえSQL Server2005に追随して近いうちに昔の事になるかもｗ
※常に確認する事が大事です。

本題に入ります。スレ主さんの場合、列を任意の数に分けてその型を数値型にし、表示する瞬間にハイフンを付加すればいいと思います。文字列と数値列では数値列の方が検索スピードは速いです。ただし、結合処理が一番時間がかかりますので「データを使用する際に工夫」が必要となります。

引用返信 編集キー/

■20069 / inTopicNo.7)

　Re[5]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ はまや (19回)-(2008/06/05(Thu) 10:23:39)

みなさん、回答ありがとうございます。

正規表現の使用というのが、いまいち理解できていませんが
C#でも同じような単語をチラっとみたような気がしますので
それと一緒に今から勉強してみたいと思います。

あとは、入力する際の工夫と別途列を作るということですが。
そもそも何故こんなところで悩んでいるかと言いますと
電話番号は最近では携帯電話もあるためFAXを含めて３件保有できる
ようにしています。
その時、市外局番の桁数が地域によって変わるため
（00-1111-2222とか000-111-2222とか0000-11-2222など）
市外局番を意識していないといけません。

まず列を分ける事を考えましたが、1番号につき３つの列を使うので
やめました。
次に検索を考えた場合、ヒットする範囲が狭くなってしまうため
数字のみの入力にしましたが、これだと市外局番が分からなくなって
しまいます。
それで行き着いた先が今の状態なのですが

列を増やしたくなくて、１つの列でアレもコレもというのは
欲張りすぎでしたかね
今のやり方ではパフォーマンスが悪いと思っているので
ここは検索用列を追加して対応したいと思います。

ありがとうございました。

解決済み

引用返信 編集キー/

■20070 / inTopicNo.8)

　Re[2]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ いしだ (122回)-(2008/06/05(Thu) 10:45:26)

2008/06/05(Thu) 10:46:07 編集(投稿者)

> SQL Server 2005なら、正規表現が使えます。
> http://msdn.microsoft.com/ja-jp/library/ms174214.aspx

Oracleは10gから正規表現機能が追加されましたが、
SQL Server 2005では簡単には行かないんではなかったでしたかね？
SQLCLRで関数作ったり等。

[編集]
解決済みつけ忘れ

解決済み

引用返信 編集キー/

■20075 / inTopicNo.9)

　Re[3]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ はつね (770回)-(2008/06/05(Thu) 11:17:34)

■No20070 (いしださん) に返信
>>SQL Server 2005なら、正規表現が使えます。
>>http://msdn.microsoft.com/ja-jp/library/ms174214.aspx
>
> Oracleは10gから正規表現機能が追加されましたが、
> SQL Server 2005では簡単には行かないんではなかったでしたかね？
> SQLCLRで関数作ったり等。

電話番号で正規表現って難しくないですか？
市外局番の桁数って同一地域で先頭からの番号列は同じでも変わるときもありますし。

解決済み

引用返信 編集キー/

■20077 / inTopicNo.10)

　Re[4]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ いしだ (124回)-(2008/06/05(Thu) 11:40:50)

> 電話番号で正規表現って難しくないですか？
> 市外局番の桁数って同一地域で先頭からの番号列は同じでも変わるときもありますし。

そうですね。難しそうですね。
Oracleで言うregexp_replaceのようなものを作成して、
[^0-9]を空文字に置換してやる方法はありそうですね。
結局Replaceとほぼ同じですが。。。

解決済み

引用返信 編集キー/

■20083 / inTopicNo.11)

　Re[6]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ やじゅ (437回)-(2008/06/05(Thu) 12:57:28)

■No20069 (はまやさん) に返信
> 電話番号は最近では携帯電話もあるためFAXを含めて３件保有できる
> ようにしています。
> その時、市外局番の桁数が地域によって変わるため
> （00-1111-2222とか000-111-2222とか0000-11-2222など）
> 市外局番を意識していないといけません。
>

ハイフンは何のために入力しようとしていますか？
見た目の見易さだけであれば、正確性にこだわる必要はないように思います。
簡易的な規則性で表示してもいい気がしますね。

正確性にこだわらないなら、最初からＤＢにハイフン無しで登録するのも
ありですね。

お客さんに提示するなら正確性にこだわる必要があるかも知れません。

電話番号の分解
http://bbs.wankuma.com/index.cgi?mode=al2&namber=17518&KLOG=35

>[^0-9]を空文字に置換してやる方法はありそうですね。
>結局Replaceとほぼ同じですが。。。

そうですね、単純にリプレースの代わりとして正規表現での提示をしたって
ところです、電話番号は難しいので･･･

解決済み

引用返信 編集キー/

■20085 / inTopicNo.12)

　Re[7]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ はまや (20回)-(2008/06/05(Thu) 13:57:30)

心が折れそうになったので、来てみたらレスが増えていたので
驚きました。
解決済みにチェックを入れましたが、もう少しだけお付き合い下さい。

調べていくとOracle 10gは確かにREGEXP_REPLACE関数などで出来る
ようでした。（REPLACEを３回やらない分速い？）
SQL Server 2005で調べていくと以下のサイトに行き着きました。

http://msdn.microsoft.com/ja-jp/magazine/cc163473.aspx
http://shizentai.jp/wordpress/?p=68

これはつまりSQL CLR統合を使えば正規表現になる検索が可能になるという
ことで間違いないでしょうか？
逆にSQL CLR統合を使わないと正規表現による検索はできないという
ことですよね？

本来の目的は別としてコレを作っておけば何かと便利そうなので
勉強がてら作ってみたいと思っているのですが
VS2005で作るプロジェクトの中身をどう作って良いのか
わかりません。
なにかヒントでもあれば教えて下さい。
よろしくお願いします。

開発環境
WindowsXP(SP2)
VS2005 C#（ADO.NET）
SQL Server 2005 Developer

引用返信 編集キー/

■20087 / inTopicNo.13)

　Re[8]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ いしだ (125回)-(2008/06/05(Thu) 14:29:22)

2008/06/05(Thu) 14:34:44 編集(投稿者)

> なにかヒントでもあれば教えて下さい。

・SQL Server 2005の新機能「SQL CLR」（著者、中さん）
http://www.atmarkit.co.jp/fdotnet/special/sqlclr02/sqlclr02_03.html

と、

・正規表現によるパターンマッチングとデータ抽出の簡略化
http://msdn.microsoft.com/ja-jp/magazine/cc163473.aspx

と、

・CLR 統合の有効化
http://msdn.microsoft.com/ja-jp/library/ms131048.aspx

を参考にしてみてはいかがでしょうか。

引用返信 編集キー/

■20098 / inTopicNo.14)

　Re[9]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ ネタ好き (372回)-(2008/06/05(Thu) 16:54:32)

正規表現は明らかにパフォーマンスを必要とする処理だから止めておいた方がいい。
それよりもハイフンの位置を列に格納したほうがいいよ。

引用返信 編集キー/

■20101 / inTopicNo.15)

　Re[10]: SQLであいまいな文字の検索範囲を広げたい

□投稿者/ はまや (22回)-(2008/06/05(Thu) 17:02:50)

出来ました！
[+]をクリックしないとソースが見れないというのに気づくのが遅くて
ずっとRegexMatch()を独自に組んでいましたが、
気づいたのでソースのコピーだけで終わりました。

その間、正規表現の書式を勉強していましたが
文字列の中から「0-9の文字だけを抽出」なんていうものは
無いんですね、結局Regex.Replace()が出てくるのですか
みなさんが言ってる意味にやった辿り付けました。

「電話番号の分解」スレで語られているように電話番号は難しいんですね
入力フォーマットの固定化から検討が必要な気がしてきました。
ネタ好きさんの言うとおり、ハイフンの位置を持つのも良いかもしれませんね

何はともあれ、SQL CLR統合は面白そうです
これを学べただけでも、かなりの収穫です。

では、改めて「解決済み」にチェックを入れたいと思います
ありがとうございました。

解決済み

引用返信 編集キー/

トピック内ページ移動 / << 0 >>

このトピックに書きこむ

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