こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

SUBSTRING 関数に渡した長さのパラメータが無効です。

 
●サーバー機
DB:SQLServer2005
OS:WindowsServer2003

●クライアント機
OS:WindowsXP SP3

住所テーブル(ADDRESS)の項目のひとつである電話番号(TELNO)を
特定の記号で3つの別項目として取得したいのですが
クライアント機の SQL Server Management Studio にてクエリを実行すると

「SUBSTRING 関数に渡した長さのパラメータが無効です。」

以上のようなエラーが発生します。

電話番号の項目は文字型の項目で、実際のデータとしては

例:(01)2345-6789
(0123)45-6789
(01234)5-6789

こんな感じで市外局番の前後に必ず"(" ")"が付いており"-"も必ず付いた状態です。
ただし、市外局番、ハイフンの前後の番号の桁数に決まりはありません。
※市外局番5桁、ハイフン前4桁、ハイフン後4桁という最大桁数の制限はあり。

【実行したクエリ】

Select
SUBSTRING(TELNO, CHARINDEX('(',TELNO)+1, CHARINDEX(')',TELNO)-CHARINDEX('(',TELNO)-1) AS TELNO1
, SUBSTRING(TELNO, CHARINDEX(')',TELNO)+1, CHARINDEX('-',TELNO)-CHARINDEX(')',TELNO)-1) AS TELNO2
, SUBSTRING(TELNO, CHARINDEX('-',TELNO)+1, 5) AS TELNO3
From ADDRESS

エラーメッセージの通り、SUBSTRING関数のパラメータの内容が長すぎるのが原因のようですが
いろいろ調べてみたのですが別の書き方が見つけられず困っている状態です。
何かいい方法はないものでしょうか?(別の関数を使用する等々)
※ストアドプロシージャで処理するのではなく1本のクエリで結果が得られるのが望ましいです。

よろしくお願いいたします。

投稿日時 - 2008-10-06 16:44:53

QNo.4381823

すぐに回答ほしいです

質問者が選んだベストアンサー

>電話番号の項目は文字型の項目で

charですか?
それともvarcharですか?

>電話番号の項目は文字型の項目で、実際のデータとしては
>例:(01)2345-6789
> (0123)45-6789
> (01234)5-6789
>こんな感じで市外局番の前後に必ず"(" ")"が付いており"-"も必ず付いた
>状態です。
>ただし、市外局番、ハイフンの前後の番号の桁数に決まりはありません。
>※市外局番5桁、ハイフン前4桁、ハイフン後4桁という最大桁数の制限はあり。

得たい結果を示しましょう。
エラーになるクエリから、回答者側で得たい結果を推測するのは容易ではありません。
当方、SQL Server 2005 Expressですが、提示されたデータ及びクエリで市外局番、市内局番、加入者番号の三つに分割した結果を正常に得られました。
データ中に、形式が正しくないものが含まれているのでは?

投稿日時 - 2008-10-06 18:43:22

補足

>charですか?
>それともvarcharですか?

char型の14桁の項目です。

>得たい結果を示しましょう。

(01)2345-6789 が入っていた場合

TELNO1 として 01
TELNO2 として 2345
TELNO3 として 6789

を取得したいのです。
電話番号以外の項目で検索条件を付加して
対象件数を絞った時はこのエラーがでない場合があります。
※この住所テーブルの件数は現状で7万件ほどあります。
(情報後出しのようになってしまい申し訳ないです。)

投稿日時 - 2008-10-06 18:53:54

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

以下のようなスクリプトでもチェックできるかな
dim cn
dim rst
dim re

set cn = createobject("ADODB.connection")
set rst = createobject("ADODB.recordset")
set re = createobject("VBScript.RegExp")
re.pattern = "^\(\d+\)\d+-\d+$"

cn.ConnectionString = "provider=sqloledb;data source=xxx;initial catalog=xxx;user id=xxx;password=xxx"
cn.Open

rst.Open "select telno from address", cn, 3
Do Until rst.EOF
if re.test(rst.fields("telno")) then
else
WScript.Echo rst.fields("telno")
end if
rst.MoveNext
Loop

投稿日時 - 2008-10-07 09:11:28

お礼

どうもありがとうございます。
参考にさせていただきます。

投稿日時 - 2008-10-07 09:46:49

ANo.2

No.1さんの書かれた通り、おそらく7万件の中にエラーデータが含まれていると思います。Transact-SQLはRegExpは使えませんが、簡単なパターンチェックはできますので、まずは以下の対象がないかどうか確認してみてください。
SELECT * FROM ADDRESS WHERE PATINDEX('(%)%-%',TELNO)=0

投稿日時 - 2008-10-07 02:58:29

お礼

 
今朝、早速データを確認してみたところ数件 01-23456-7890
というような規定のフォーマットに沿わないデータが存在しました。
エラーメッセージの文言から判断して、データ自体には不具合はないと判断してしまっていたのが間違いでした。

jamshid6さんに提示していただいたチェッククエリを参考に以下のようにクエリを修正しました。

Select
, CASE WHEN PATINDEX('(%)%-%',TELNO) <> 0 THEN SUBSTRING(TELNO, CHARINDEX('(',TELNO)+1, CHARINDEX(')',TELNO)-CHARINDEX('(',TELNO)-1) ELSE '' END AS TELNO1
, CASE WHEN PATINDEX('(%)%-%',TELNO) <> 0 THEN SUBSTRING(TELNO, CHARINDEX(')',TELNO)+1, CHARINDEX('-',TELNO)-CHARINDEX(')',TELNO)-1) ELSE '' END AS TELNO2
, CASE WHEN PATINDEX('(%)%-%',TELNO) <> 0 THEN SUBSTRING(TELNO, CHARINDEX('-',TELNO)+1, 5) ELSE '' END AS TELNO3
From ADDRESS


chukenkenkouさん、jamshid6さん どうも有難うございました。
 

投稿日時 - 2008-10-07 09:44:30