« Mac De Oracle : Oracleのバージョン確認(FAQ) #5 | トップページ | Mac De Oracle : Oracleのバージョン確認(FAQ) #6 »

2007年4月26日 (木)

Mac De Oracle : Ask Discus : 問い合わせた結果、ある列(文字列)の右端2文字でソートするには?

Ask Tomのパクリ! なんてことはやりませんが、ウチの奥さんからヘルプメールが来たので。

今、Oracle10g R2 + Seaser2 + Hibernate だっけ? を使ったプロジェクトで奮闘しているウチの奥さんから SQL文に関するヘルプメールが来たので、 Ask Discus という Ask Tomのパクリ風? ヘルプを一つ。 
FAQだと思うのでFAQカテゴリにしておきます。


You Asked...
ウチの奥さんからのヘルプ内容:

問い合わせた結果をソートする必要がある。
そして、ソート条件は、varchar2型として定義されている列で、右端から2文字で昇順にソートする。
この場合、order by句はどのように書けばいいの?  
おしえて Discus! (ダーリン!。。。かも。。。笑い)


という質問。

and I said...

簡単なんだけど。。(知らない人には簡単では無いかも。。。。)

order by句でもスカラ関数が利用できるので、 substr()を利用して可変長文字列の右端2文字を取り出せば OK.

では、お約束のscottユーザに接続し、emp表の ename列の右端2文字で昇順にソートしてみよう!

length()を利用しなくても substr()の第二引数に負の値を指定すれば簡単にできるということを忘れていた!!
こちらの方がスマートです。コメントくださった方に感謝します。

SCOTT> l
1 select
2 emp.*,
3 substr(ename,-2) as right2chars
4 from
5 emp
6 where
7 empno < 8000
8 order by
9* substr(ename,-2)
SCOTT> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RIGHT2CHARS
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
7905 JEAN 7904 AN
7901 SADE 7876 DE
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 EN
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 ER
7934 MILLER CLERK 7782 82-01-23 1300 10 ER
7566 JONES MANAGER 7839 81-04-02 2975 20 ES
7900 JAMES CLERK 7698 81-12-03 950 30 ES
7903 ALEX 7901 EX
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 IN
7698 BLAKE MANAGER 7839 81-05-01 2850 30 KE
7876 ADAMS CLERK 7788 87-05-23 1100 20 MS
7839 KING PRESIDENT 81-11-17 5000 10 NG
7521 WARD SALESMAN 7698 81-02-22 1250 500 30 RD
7902 FORD ANALYST 7566 81-12-03 3000 20 RD
7782 CLARK MANAGER 7839 81-06-09 2450 10 RK
7369 SMITH CLERK 7902 80-12-17 800 20 TH
7788 SCOTT ANALYST 7566 87-04-19 3000 20 TT
7904 DISCUS 7903 US
7907 Wendy 7906 dy
7906 Duke 7905 ke

20行が選択されました。

この方法でもできるんだが、length()関数を使わないので上記の方法がいいですね。

SCOTT> l
1 select
2 emp.*,
3 substr(ename,length(ename)-1,2) as right2chars
4 from
5 emp
6 order by
7* substr(ename,length(ename)-1,2)
SCOTT> /


ちなみに、NLSSORTを利用すれば、言語ソートも可能。 
length()関数を利用しないよう修正。

SCOTT> select 
2 emp.*,
3 substr(ename,-2) as right2chars
4 from
5 emp
6 where
7 empno between 8000 and 8010
8 order by
9 substr(ename,-2)
10 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RIGH
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----
8002 かきくあい あい
8001 おえういあ いあ
8000 あいうえお えお
8008 カキクアイ アイ
8007 オエウイア イア
8006 アイウエオ エオ
8004 カキクアイ アイ
8005 オエウイア イア
8003 アイウエオ エオ

9行が選択されました。

SCOTT> l
1 select
2 emp.*,
3 substr(ename,-2) as right2chars
4 from
5 emp
6 where
7 empno between 8000 and 8010
8 order by
9 nlssort(
10 substr(ename,-2),
11 'NLS_SORT=JAPANESE_M'
12* )
SCOTT> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RIGH
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----
8002 かきくあい あい
8008 カキクアイ アイ
8004 カキクアイ アイ
8001 おえういあ いあ
8007 オエウイア イア
8005 オエウイア イア
8000 あいうえお えお
8006 アイウエオ エオ
8003 アイウエオ エオ

9行が選択されました。


NLSSORT()関数や言語ソートの詳細はマニュアルを参照していろいろと試してみてくださいね。。
Oracle Database グローバリゼーション・サポート・ガイド 10g リリース2(10.2) 言語ソート
Oracle Database グローバリゼーション・サポート・ガイド 10g リリース2(10.2) 言語ソートパラメータ
Oracle Database グローバリゼーション・サポート・ガイド 10g リリース2(10.2)言語ソートの使用
Oracle Database グローバリゼーション・サポート・ガイド 10g リリース2(10.2) NLSSORT関数

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/14851677

この記事へのトラックバック一覧です: Mac De Oracle : Ask Discus : 問い合わせた結果、ある列(文字列)の右端2文字でソートするには?:

コメント

substrの2番目の引数に負の値を指定すると後ろから数えてくれそうな気がしますがどうでしょう。今手元に確認できる環境はないのですが、マニュアル上はできるような気がしないでもないです。

投稿: | 2007年5月 2日 (水) 11時56分

If position is negative, then Oracle counts backward from the end of char.

マニュアルには上記の通り、負の値を指定すると逆からカウントするとなっていますから、できますね。
負の値を指定する方がスマートですね。コメントありがとうございます。

投稿: discus | 2007年5月 2日 (水) 14時28分

コメントを書く