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

質問

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

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

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

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

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

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

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

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

【実行したクエリ】

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関数のパラメータの内容が長すぎるのが原因のようですが
いろいろ調べてみたのですが別の書き方が見つけられず困っている状態です。
何かいい方法はないものでしょうか？（別の関数を使用する等々）
※ストアドプロシージャで処理するのではなく１本のクエリで結果が得られるのが望ましいです。

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

回答No.1

chukenkenkou · ベストアンサー

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

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

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

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

補足

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

char型の14桁の項目です。

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

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

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

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

回答No.3

nora1962

以下のようなスクリプトでもチェックできるかな
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

お礼

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

回答No.2

jamshid6

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

お礼

今朝、早速データを確認してみたところ数件 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さん　どうも有難うございました。