SQLのORDERBY句について質問です。

質問

SQLのORDERBY句について質問です。

データをSQLで並べ替えたいのですが、
AB-125
のような商品名のデータを並べ替えたいと考えています。
しかし、
AB-125
AB-123
AB-20
となってしまった為、別スレッドでアドバイスいただき、
Order by Left([商品名], InStr(1, [商品名], "-")-1), CLng(Mid([商品名], InStr(1, [商品名], "-")+1));
と追記いたしました。
一瞬うまくいったと思ったのですが、商品コードの中に、
AB-125
AB-123A
AB-10M
の様に最後にも文字が挿入されていました。
この数字の部分だけ取り出す方法か、もしくはこのような商品コードを
うまく並べ替えできる Order by 句のアドバイスよろしくお願いいたします。

回答No.4

30246kiku · ベストアンサー

＃２です

> やはり、商品コードの命名規則を決めておかないと駄目ですね。

そうですね。
数字部分も桁数を揃えておくと、単純に Order by [商品名]　でできると思います。

データはそのままで、並び替える関数を作ってみました。
（動くというだけで、使えるかどうかは判断してください）

ORDER BY への記述）

ORDER BY GetOrderVal(1,商品名), GetOrderVal(2,商品名), GetOrderVal(3,商品名)

標準モジュールへの自作関数記述例）

Public Function GetOrderVal(iNum As Long, vStr As Variant) As Variant
　　Dim sNum As String, sStr As String
　　Dim sTmp As String
　　Dim i As Long
　　Const SEP As String = "-"

　　On Error Resume Next
　　GetOrderVal = Null
　　If (VarType(vStr) <> vbString) Then Exit Function

　　Select Case iNum
　　　　Case 1
　　　　　　GetOrderVal = Left(vStr, InStr(vStr & SEP, SEP) - 1)
　　　　Case 2
　　　　　　sNum = ""
　　　　　　For i = InStrRev(SEP & vStr, SEP) To Len(vStr)
　　　　　　　　sTmp = Mid(vStr, i, 1)
　　　　　　　　If (sTmp Like "[0-9]") Then sNum = sNum & sTmp
　　　　　　Next
　　　　　　GetOrderVal = Val(sNum)
　　　　　　If (IsError(GetOrderVal)) Then GetOrderVal = Null
　　　　Case 3
　　　　　　sStr = ""
　　　　　　For i = InStrRev(SEP & vStr, SEP) To Len(vStr)
　　　　　　　　sTmp = Mid(vStr, i, 1)
　　　　　　　　If (sTmp Like "[!0-9]") Then sStr = sStr & sTmp
　　　　　　Next
　　　　　　GetOrderVal = sStr
　　End Select
End Function

処理の切り分けは、第１引数にて
１：　"-" 前の文字列を返す
２：　"-" 後の数値を返す
３：　"-" 後の数字以外を返す

２において、AB-33 / AB-33S / AB-X33 / AB-3A3B 等では同じ 33 を返します。
３では、上記例で "" / "S" / "X" / "AB" を返します。

> しかし、AB-X33

AB-33X と同意でしょうか。
Excelのデータをテーブルに取り込んでいるのなら、更新クエリで書き換えてみてはどうでしょうか。
この時、数字部分の桁を揃えておくのも手だと思います。

更新クエリの雰囲気は、"-" 後が数字じゃない時を対象にするのなら

UPDATE テーブル名 SET 商品名 = XXXX[(商品名])
WHERE Mid([商品名], InStr([商品名],"-")+1, 1) Like "[!0-9]";

関数 XXXX は、新しく作ります。

無条件に書き換えるなら（数値の桁も合わせてみると）
前述した　GetOrderVal　を使って見ると（関数は１つにした方が速くなると思いますが）

UPDATE テーブル名 SET 商品名 = GetOrderVal(1,商品名) & "-" & Format(GetOrderVal(2,商品名),"00000") & GetOrderVal(3,商品名);

使えるものを作ってください。

回答No.6

layy

「商品名」が格納されているマスタテーブル「商品マスタ」とかはないのでしょうか。
あればそこにソート用の項目を新たに用意すると将来的にも汎用性あると思います。

ａｃｃｅｓｓにしてもＳＱＬサーバにしても、
一部の文字列を抜きとる処理をしないと先に進みません。

「商品名」で困難なら、商品コード＋αとか
他の項目で代用できればそれでも構いませんが・・・。

回答No.5

30246kiku

＃４です

すみません。
Accessとして考えてました。以外ならスルーしてください。

回答No.3

layy

不規則なら規則性あるものにすること。

まず前半後半２項目に分ける、数字だけの項目、を準備するのがよいのでは。並び自在にできます。自作モジュールで、そう複雑でないし、数字だけ、英字だけ取り出すこと、も可能です。

既存のやり方では、実際の並びに使われた.125や123や10Mの値は内部で編集されて見えません。
並びに使われる値はやはりテーブルやクエリで目視できる状態にしておくのが良いです。

回答No.2

30246kiku

CLng　を　Val　に変更してみては？

ORDER BY の３つ目として [商品名] を追加すれば良いのでしょうか。

Order by Left([商品名], InStr(1, [商品名], "-")-1), Val(Mid([商品名], InStr(1, [商品名], "-")+1)), [商品名];

お礼

うまくいきました。m(_ _)m
しかし、AB-X33
みたいな商品コードの場合にエラーが発生してしまいます。
やはり、商品コードの命名規則を決めておかないと駄目ですね。

お年を召した方達が一生懸命Excelで作ってできたデータなので、仕方ないのですが(^^;)
ちなみに上記の様な
AB-33
AB-33S
AB-X33
みたいな場合はSQLで対処できますでしょうか？

回答No.1

layy

前のスレッドを見ていないのでわかりませんが、
前半は「ＡＢ」しかないのでしょうか？。

補足

前半は
AB-
B-
DBI-
など桁も表示もまちまちになっています。
後半の文字は基本的にアルファベット大文字が
1文字もしくは2文字になっています。