none
SQL Server 文字列に対してのorder byについて(数値・文字混在の場合) RRS feed

  • 質問

  • いつもお世話になっております。

    掲題について、ネット等で調べて見たのですが、答えがみつからず質問させていただきます。

    SQL Serverのテーブルで文字列をソートしてselectしたいのですが、その文字列には数値と文字が混在しており、想定した並び順にソートする為に、

    以下のsqlを実行してみました。

    table1

    項目 A : decimal(6,0)、B : varchar(50)、C : varchar(8)、D : smallint

    項目Cを昇順にソート。項目Cには、"1","2","02","10","-1","A1","&2","17-6"・・・・と数値・文字混在

    実行sql

    select * from table1

    order by CASE WHEN ISNUMERIC(C) = 1 THEN CAST(C as int) ELSE C END

    実行結果 : エラーで完了。varcharの値 '17-6' をデータ型 int に変換できませんでした。

    ISNUMERIC(C)の問題かと思い、以下のsqlを実行し検証。

    select * from table1 where ISNUMERIC(C) = 1

    するとselectされた項目Cの値は全て数値でした。(もちろん、'17-6' はselectされていませんでした。)

    where句とorder by句とでは、ISNUMERICの動きが違うのでしょうか?

    初歩的な質問で恐縮ですが、ご教授のほどよろしくお願いします。

    また、数値と文字混在のソートで他に何か方法があれば、合わせてお教えいただければ幸いです。

    2014年2月14日 6:20

回答

  • mihasato25 さま よろしく。

    ご質問から外れるのですが、基本的な構文について、
    msdn CASE (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms181765.aspx によると、

     else_result_expression と任意の result_expression のデータ型は同一であるか、
     暗黙的な変換によって同一の型になる必要があります。

    CASE 句で、これは守られていませんよね。 片や数値、片や文字になってませんか。
    これでは、思った様に、動いていないんじゃぁ、ないでしょうか。

    ISNUMERIC の動きが違う と言う事はないと思いますよ。
    • 編集済み ShiroYuki_Mot 2014年2月14日 7:53 最後の1行を追加
    • 回答としてマーク mihasato25 2014年2月14日 7:54
    2014年2月14日 7:38
  • 正確なソートの仕様がわからないのですが、以下のような感じで良いのかな?

    (select 項目C, 0 as 区分, ROW_NUMBER() OVER(order by CAST(項目C as int)) as 番号
                                     from table1
                                     where isnumeric(項目C) = 1)
    union
    (select 項目C, 1 as 区分, ROW_NUMBER() OVER(order by 項目C) as 番号
                                     from table1
                                     where isnumeric(項目C) <> 1)
    order by 区分, 番号


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/


    • 編集済み trapemiya 2014年2月14日 9:02 コードを見やすいように整形
    • 回答としてマーク mihasato25 2014年2月17日 6:03
    • 回答としてマークされていない mihasato25 2014年2月17日 6:04
    • 回答としてマーク mihasato25 2014年2月17日 6:05
    2014年2月14日 9:00

すべての返信

  • mihasato25 さま よろしく。

    ご質問から外れるのですが、基本的な構文について、
    msdn CASE (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms181765.aspx によると、

     else_result_expression と任意の result_expression のデータ型は同一であるか、
     暗黙的な変換によって同一の型になる必要があります。

    CASE 句で、これは守られていませんよね。 片や数値、片や文字になってませんか。
    これでは、思った様に、動いていないんじゃぁ、ないでしょうか。

    ISNUMERIC の動きが違う と言う事はないと思いますよ。
    • 編集済み ShiroYuki_Mot 2014年2月14日 7:53 最後の1行を追加
    • 回答としてマーク mihasato25 2014年2月14日 7:54
    2014年2月14日 7:38
  • ShiroYuki_Mot 様 さっそくの返信、ありがとうございました。

    言われてみればそうですね。納得しました。

    そうなると、数値と文字混在のソートは無理そうなので、

    ソートキー項目を追加してみます。

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

    2014年2月14日 7:53
  • 正確なソートの仕様がわからないのですが、以下のような感じで良いのかな?

    (select 項目C, 0 as 区分, ROW_NUMBER() OVER(order by CAST(項目C as int)) as 番号
                                     from table1
                                     where isnumeric(項目C) = 1)
    union
    (select 項目C, 1 as 区分, ROW_NUMBER() OVER(order by 項目C) as 番号
                                     from table1
                                     where isnumeric(項目C) <> 1)
    order by 区分, 番号


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/


    • 編集済み trapemiya 2014年2月14日 9:02 コードを見やすいように整形
    • 回答としてマーク mihasato25 2014年2月17日 6:03
    • 回答としてマークされていない mihasato25 2014年2月17日 6:04
    • 回答としてマーク mihasato25 2014年2月17日 6:05
    2014年2月14日 9:00
  • trapemiya 様 サンプルありがとうございました。

    ソート仕様はもっと複雑なので、区分を増やす事で対応できました。

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

    2014年2月17日 6:10