いつもお世話になっております。
環境はバックエンドに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ベストアンサー
- 回答日時:
参考に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関数を使って文字コードを直接調べる方法に変えました。
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
No.8
- 回答日時:
ちょっと違ったやり方を
(1)「.NET Framework v3.5」がインストールされていることを確認する。(C:\WINDOWS\Microsoft.NET\Framework\v3.5\vbc.exe」が存在する)
「.NET Framework v4.0」は不可。
(2)以下の内容のファイル「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
(3)「C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlaccess.dll」があることを確認。
(4)コンパイル
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
(6)続いてDLLの登録。使用するDBを選択して実行。
USE データベース名
GO
CREATE ASSEMBLY [RegxReplace]
FROM '作成したDLLファイルのフルパス'
WITH PERMISSION_SET = SAFE
GO
(7)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
(9)order by句を組み立てる
order by
cast( dbo.RegxReplace( 項目名, '\d', '') as varchar(20) ),
cast( dbo.RegxReplace( 項目名, '[^\d]', '') as int )
nora1962様
ご回答をありがとうございます。
なるほど!というご回答ですね。
私にとってはご回答の1行1行をじっくり読んでいかないと
理解もおぼつかないレベルなのですが、レベルアップのため
に参考にさせていただきます。
自分の知っている範囲というのも、たかがしれていますね。^^;
No.6
- 回答日時:
こんばんは。
文字列は辞書順でソートするからね・・・。
select *
from テーブル
order by cast(名称 as int)
は?
試してないけど・・・。
null入ってたら落ちるのかな?
taka451213様
ご回答ありがとうございました。
そうですね。
この問題を機会にソート順の勉強ができました。
いままで、見てみぬふりをしていたのですが、ユーザから
要望があがれば、対処せざるおえなくなっていまったのです。
>order by cast(名称 as int)
ですが、文字列にハイフンなどが使われているとだめなようです。
ご教示ありがとうございました。
No.5
- 回答日時:
やりたいことの意味がより不明になってきました。
。さっきの例で、a1b-12 の場合
文字群として判断するのは、
単純に数字でないもの ab か
文字に挟まれる数字も文字群として捉えるのか a1b
数字の方も同様にその逆で
112 か 12 のどっちなのでしょうか (^^ゞ
それとも・・・?
※SQLSERVERは全くの門外漢ですが
なんにせよカラム(フィールドの)値を一文字ずつ読んで行って
二つに振り分けるしかないのでは?
nicotinism様
なんというか、わかりにくい質問にお付き合いいただき、ありがとうございます。
二つのレコードの値がそれぞれab10とab2であった場合、普通にorder byすれば
ab10
ab2
というふうに並びます。
しかし、数字だけを見ますと2と10は10のほうが大きいのでこの場合は
ab2
ab10
とならんでほしいということなのですが、説明できているか自信がありません。
>※SQLSERVERは全くの門外漢ですが
>なんにせよカラム(フィールドの)値を一文字ずつ読んで行って
>二つに振り分けるしかないのでは?
おっしゃる通りのように思えてきました。
ストアドで実現できると思うので、調べているところです。
ありがとうございました。
No.4
- 回答日時:
難しいからその難しい部分だけ他に託す意味でアクセス。
アクセスで文字列、数値と並びに必要な項目を付与してServerに戻す、もしくはそれでもうアクセスにデータを渡す。
何か基準、ネタがないと並びはできないでしょう。
それでもやるならオリジナルの並びかえプログラム作成を考える。
layy様
再度のご回答ありがとうございます。
私はストアドは使ったことがないのですが、SQLServer側でストアドを使って
一時テーブルがつくれないか考えています。
文字列中を検索していき、最初の数字群が見つかったところで、その数字群を
10桁くらいの数字(ゼロで始まる)に置き換えてやり、次にまた数字群がみつかれば
また同じ操作をする、などということを検討しています。そうすれば、最初の文字が
同じ項目値の場合、数字の大小でOrderByかけられそうな気がします。項目の値自体は
それほど長くはないので、こんな方法もありかと思います。
ストアドは初めてなので、できるかわかりませんが、現在調査中です。
もし、よろしければ、その観点でアドバイスいただければと思います。
No.1
- 回答日時:
> SQLでの並べ替えを数字の順番にしたい
とありますが「a-10」が「dgc1」より小さいという理由が分かりません。
「-10」を切りだしてマイナスとして評価するという訳でもないようですし。
そこのあたりを説明してもらえないと、回答しようがないです。
この回答への補足
nora1962様
早速のご回答ありがとうございます。
説明不足、ご容赦ください。
通常、SQLでorder byをかけると、一文字目から評価していくと思います。
aとdを比べて、単純にaがさきにくるということがいいたかったのです。
項目の中身は文字、数字などまったく規則がないためハイフンなども
入ってくる可能性があります。
この説明でご了解いただけるでしょうか?
申し訳ありません。
やはり説明不足でした。
項目の中身は文字と数字などが不規則にならびますが、文字部分は普通に並べ替えて
数字が現れる部分についてだけ、数字の大小で並べ替えたいのです。
> SQLでの並べ替えを数字の順番にしたい
全レコードを通して、その中の数字の順番で並べ替えたいのではなく、例えば、
先頭の文字列が同一なもののなかで、それに続く数字については大小で昇順に並べたい
ということです。
わかりにくい表現、申し訳ありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
エクセルVBAで5行目からオート...
-
Access テキスト型に対する指定...
-
Oracle 2つのDate型の値の差を...
-
セルの右クリックで出る項目を...
-
BIOSでAHCIに設定したいが、項...
-
エクセルグラフの凡例スペース
-
必須入力項目と入力必須項目
-
Accessレポートで特定条件で改...
-
Accessで数値型にNULLをInsert...
-
APN設定について教えていただけ...
-
Accessの表形式のフォームについて
-
access2000:フォームで入力し...
-
他テーブルで一致する列から名...
-
Access又はExcelで256項目を超...
-
SQLでの変数の扱いについて
-
SQL*Loaderでのゼロ埋め
-
エクセルで横スクロールをジャ...
-
jwwのクロスラインカーソルの角...
-
Joy To Keyのマウスやキーボー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
エクセルVBAで5行目からオート...
-
セルの右クリックで出る項目を...
-
Access テキスト型に対する指定...
-
Oracle 2つのDate型の値の差を...
-
SUBSTRING 関数に渡した長さの...
-
必須入力項目と入力必須項目
-
APN設定について教えていただけ...
-
エクセルグラフの凡例スペース
-
VBAで複数の数式セルを最終行ま...
-
空白をそのままインポートする...
-
BIOSでAHCIに設定したいが、項...
-
アンドロイド おサイフケータイ...
-
Accessで数値型にNULLをInsert...
-
複数のレコードを1つのレコード...
-
ORACLEでLONG項目からCHAR項目...
-
SELECT文でLEFT関数を使うと未...
-
Access又はExcelで256項目を超...
-
Accessレポートで特定条件で改...
-
2行目を表示できますか?
おすすめ情報