SQLでの並べ替えを数字の順番にしたい

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

環境はバックエンドにSQLServer2008ExpressでフロントエンドにAccess2003を使っています。
以下のような項目で並べ替えをしたいのですが、お知恵を貸してください。

名称
--------
dgc300
dgc1
a-2
a-10

これを以下のように並べ替えたいのです。

名称
--------
a-2
a-10
dgc1
dgc300

ワークテーブルを一時的につくり、そこにソート用の項目を以下のように
作ればいいところまでは調べました。

名称　　　　　　　並替用項目
-----------------------
dgc300　　　　　　　　4
dgc1　　　　　　　　　　3
a-2　　　　　　　　　　1
a-10　　　　　　　　　2

この一時テーブルの並替用項目でorder by　すればいいのですが、
名称に規則性がないものですから、一時テーブルを作るときの
並替用項目のVBAでの作成の仕方に悩んでおります。

ご教授いただければ幸いです。
どうかよろしくお願いいたします。

No.7 ベストアンサー 回答者： t-ka 回答日時： 2011/02/22 03:54

参考にSQLSERVER用に関数を作りました。

abc3 は abc00003　
abc300　は abc00300　になります。

細かいツッコミはなしということでご容赦願います。


create function test (@var as nvarchar(100))

returns nvarchar(100)
as
begin

declare @v as nvarchar(100) ,@i int
declare @vv as nvarchar(100),@vn as nvarchar(100),@vnn as nvarchar(100)

set @i = 0
set @v = ''
set @vv = ''
set @vn = ''
set @vnn = ''
set @i = datalength(cast( @var as varchar(50)))

while @i > 0
begin

set @v = right(left(reverse(@var),@i),1)

if isnumeric(@v) = 0

begin
set @vv = @vv + @v

end
else
begin

set @vn = @vn + @v

end


set @i = @i - 1
end


set @vnn = substring(cast( cast(@vn as int) + cast('100000' as int) as varchar(50)),2,5)

return @vv + @vnn

end

この回答への補足

t-ka様のご提示いただいたisnumeric関数はハイフンを無視するようです。
ですので、ASCII関数を使って文字コードを直接調べる方法に変えました。

補足日時：2011/02/22 17:26

この回答へのお礼

t-ka様

ご回答ありがとうございました。
また、コードの提示までいただき、非常に感謝しております。
ストアドや関数をSQLServerで使うのは初めてだったので、とても参考になりました。

で、ご提示いただいたコードを参考に自作で関数をつくり、なんとかうまくいきました。
以下がそのコードです。

突貫工事で作ったので、非常にみずらくなっています。
もうちょっと洗練されたコードを書きたいのですが、今日はもう限界です。。。

非常に参考になりました。
ありがとうございます。

-------------------------------------------------------------------------------
create function dbo.RepNumToOrder (@var as nvarchar(100))

returns nvarchar(100)
as
begin

declare @temp as nvarchar(2);
declare @tempstring as nvarchar(100);
declare @returnstring as nvarchar(100);
declare @i as int;
declare @iFlg as int;
declare @iConst as int;

set @temp = '';
set @tempstring = '';
set @returnstring = '';
set @iFlg = 0;
set @iConst = 10;

set @i = 1;

while @i <= LEN(@var)
begin

--一文字抽出
set @temp = substring(@var, @i, 1);

--数値でない場合
if ASCII(@temp) < 48 or ASCII(@temp) > 57

begin
--数値を抜けた場合
if @iFlg = 1
begin
set @returnstring = @returnstring + REPLICATE('0',@iConst - LEN(@tempstring)) + @tempstring;
set @returnstring = @returnstring + @temp;
set @tempstring = '';
set @iFlg = 0;
end
--直前の文字が非数値の場合
else
begin
set @returnstring = @returnstring + @temp;
end
end
--数値の場合
else

begin
--直前の文字が非数値の場合
if @iFlg = 0
begin
set @iFlg = 1;
end
--最後の文字だった場合
if @i = LEN(@var)
begin
set @returnstring = @returnstring + REPLICATE('0',@iConst - LEN(@tempstring) - 1) + @tempstring;
set @returnstring = @returnstring +@temp;
end
else
begin
set @tempstring = @tempstring + @temp;
end
end

set @i = @i + 1;

end

return @returnstring;

end

お礼日時：2011/02/22 17:13

No.8 回答者： nora1962 回答日時： 2011/02/22 11:43

ちょっと違ったやり方を

（１）「.NET Framework v3.5」がインストールされていることを確認する。（C:\WINDOWS\Microsoft.NET\Framework\v3.5\vbc.exe」が存在する）
「.NET Framework v4.0」は不可。
（２）以下の内容のファイル「RegxReplace.vb」を保存する。
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Class RegxReplace
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function RegxReplace(s As SqlString,r As SqlString,d As SqlString ) As SqlString
Dim source As String = s.ToString()
Dim regx As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(r)
Dim ret As SqlString = new SqlString(regx.Replace(source, d.ToString()))
Return ret
End Function
End Class

（３）「C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlaccess.dll」があることを確認。
（４）コンパイル
vbc /t:librarly /r:"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlaccess.dll" RegxReplace.vb
「RegxReplace.dll」が出来ればOK
（5）SQLCMDからManagementStudioで以下のSQLを発行してSQLCLRを有効にする。
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
（６）続いてDLLの登録。使用するDBを選択して実行。
USE データベース名
GO
CREATE ASSEMBLY [RegxReplace]
FROM '作成したDLLファイルのフルパス'
WITH PERMISSION_SET = SAFE
GO
（７）SQLで使えるように関数の公開
CREATE FUNCTION RegxReplace( @str nvachar(max), @regx nvarchar(max), @repnvarchar(max) ) RETURNS　nvarchar(max)
EXTERNAL　NAME　RegxReplace.RegxReplace.RegxReplace
GO
(8)試してみる
・数字を削除
select cast( dbo.RegxReplace( 'ABCDEFG123','\d', '') as varchar(20) )
→'ABCDEFG'
・数字以外を削除
select cast( dbo.RegxReplace( 'ABCDEFG123','[^\d]', '') as int )
→123
（９）order by句を組み立てる
order by
cast( dbo.RegxReplace( 項目名, '\d', '') as varchar(20) ),
cast( dbo.RegxReplace( 項目名, '[^\d]', '') as int )

この回答へのお礼

nora1962様

ご回答をありがとうございます。

なるほど！というご回答ですね。
私にとってはご回答の1行1行をじっくり読んでいかないと
理解もおぼつかないレベルなのですが、レベルアップのため
に参考にさせていただきます。

自分の知っている範囲というのも、たかがしれていますね。^^;

お礼日時：2011/02/22 17:22

No.6 回答者： taka451213 回答日時： 2011/02/21 22:09

こんばんは。

文字列は辞書順でソートするからね・・・。

select *
from テーブル
order by cast(名称 as int)

は？
試してないけど・・・。
null入ってたら落ちるのかな？

この回答へのお礼

taka451213様

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

そうですね。
この問題を機会にソート順の勉強ができました。
いままで、見てみぬふりをしていたのですが、ユーザから
要望があがれば、対処せざるおえなくなっていまったのです。

＞order by cast(名称 as int)

ですが、文字列にハイフンなどが使われているとだめなようです。

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

お礼日時：2011/02/22 17:08

No.5 回答者： nicotinism 回答日時： 2011/02/21 17:27

やりたいことの意味がより不明になってきました。

。
さっきの例で、a1b-12 の場合
文字群として判断するのは、
単純に数字でないもの ab か
文字に挟まれる数字も文字群として捉えるのか a1b

数字の方も同様にその逆で
112 か 12 のどっちなのでしょうか (^^ゞ

それとも・・・？

※SQLSERVERは全くの門外漢ですが
なんにせよカラム（フィールドの）値を一文字ずつ読んで行って
二つに振り分けるしかないのでは？

この回答へのお礼

nicotinism様

なんというか、わかりにくい質問にお付き合いいただき、ありがとうございます。

二つのレコードの値がそれぞれab10とab2であった場合、普通にorder byすれば

ab10
ab2

というふうに並びます。
しかし、数字だけを見ますと2と10は10のほうが大きいのでこの場合は

ab2
ab10

とならんでほしいということなのですが、説明できているか自信がありません。

>※SQLSERVERは全くの門外漢ですが
>なんにせよカラム（フィールドの）値を一文字ずつ読んで行って
>二つに振り分けるしかないのでは？

おっしゃる通りのように思えてきました。
ストアドで実現できると思うので、調べているところです。
ありがとうございました。

お礼日時：2011/02/21 17:53

No.4 回答者： layy 回答日時： 2011/02/21 15:51

難しいからその難しい部分だけ他に託す意味でアクセス。

アクセスで文字列、数値と並びに必要な項目を付与してServerに戻す、もしくはそれでもうアクセスにデータを渡す。

何か基準、ネタがないと並びはできないでしょう。

それでもやるならオリジナルの並びかえプログラム作成を考える。

この回答へのお礼

layy様

再度のご回答ありがとうございます。

私はストアドは使ったことがないのですが、SQLServer側でストアドを使って
一時テーブルがつくれないか考えています。

文字列中を検索していき、最初の数字群が見つかったところで、その数字群を
10桁くらいの数字（ゼロで始まる）に置き換えてやり、次にまた数字群がみつかれば
また同じ操作をする、などということを検討しています。そうすれば、最初の文字が
同じ項目値の場合、数字の大小でOrderByかけられそうな気がします。項目の値自体は
それほど長くはないので、こんな方法もありかと思います。

ストアドは初めてなので、できるかわかりませんが、現在調査中です。

もし、よろしければ、その観点でアドバイスいただければと思います。

お礼日時：2011/02/21 16:57

No.3 回答者： nicotinism 回答日時： 2011/02/21 15:39

名称の前半は文字、後半は数字であると決め打ちできるなら

文字部分を取り出すのと、数字部分を数値として取り出す
自前の関数を二個つくってクエリ中で使えばできそうですが
a1b-12 なんてのが在ればどうしようもない様な気がします。

この回答へのお礼

nicotinism様

>a1b-12 なんてのが在ればどうしようもない様な気がします。

確かにそのとおりなのです。
文字、数字が入り乱れておりますもので、、、

お礼日時：2011/02/21 16:46

No.2 回答者： layy 回答日時： 2011/02/21 13:14

ACCESSVBA側で並べることは考えてませんか。

並びに必要な項目付与だけでも、できればそうする。仕様変更の可能性もあるだろうし。

この回答への補足

layy様

ご回答ありがとうございます。
現在、VBAでコーディングしてワークテーブルを作成する方法と、
SQLSever側でなんとかできないか調べております。

SQLServerでなんとかできれば簡単なのですが、なにせ項目の内容
が文字列、数値と混在なもので、ちょっと難しいのかな、と思って
おります。

補足日時：2011/02/21 14:36

No.1 回答者： nora1962 回答日時： 2011/02/21 10:33

> SQLでの並べ替えを数字の順番にしたい

とありますが「a-10」が「dgc1」より小さいという理由が分かりません。
「-10」を切りだしてマイナスとして評価するという訳でもないようですし。
そこのあたりを説明してもらえないと、回答しようがないです。

この回答への補足

nora1962様

早速のご回答ありがとうございます。
説明不足、ご容赦ください。

通常、SQLでorder byをかけると、一文字目から評価していくと思います。
aとdを比べて、単純にaがさきにくるということがいいたかったのです。
項目の中身は文字、数字などまったく規則がないためハイフンなども
入ってくる可能性があります。

この説明でご了解いただけるでしょうか？

補足日時：2011/02/21 10:58

この回答へのお礼

申し訳ありません。
やはり説明不足でした。

項目の中身は文字と数字などが不規則にならびますが、文字部分は普通に並べ替えて
数字が現れる部分についてだけ、数字の大小で並べ替えたいのです。

> SQLでの並べ替えを数字の順番にしたい

全レコードを通して、その中の数字の順番で並べ替えたいのではなく、例えば、
先頭の文字列が同一なもののなかで、それに続く数字については大小で昇順に並べたい
ということです。

わかりにくい表現、申し訳ありません。

お礼日時：2011/02/21 11:13