Oracleの関数をSQL Serverで使う

db tool

2009年3月追記：

2010年1月追記：

マイコミジャーナルの記事「Transact-SQLの関数を理解しよう」のインデックスです

最近はSQL Serverを使っていますが、日付型を文字列に変換するT-SQLのConvert関数(MSDN)がなかなか覚えられないので、Oracleの時に使っていたTO_CHAR関数みたいなコードがないか探してみました。ココとか、ココとか、ココとか、似たようなコード等はあったのですが、ちょっとずつ機能不足な感じでした。自分で書こうかと思ったのですが、SQL Server Migration Assistant（SSMA）のことを思い出したので、インストールしてみました。
SSMAは、Oracle(8〜10g)からSQL Server(2000〜2005)へデーターベースを移行するためのマイグレーションツールで、Microsoftから無償でダウンロードできます。SSMAのダウンロードファイル「SSMAV2-JA.zip」は次の2つから構成されます。

  1. SSMA-Setup-2.0.0.420.exe
  2. SSMAExtPack-2.0.0.420.exe
    • Oracleプログラムを動かすための拡張機能。
    • セットアップ時にインストール先のSQL Serverを指定すると、「sysdb」というデータベースが作られ、Oracle互換用のテーブル、ビュー、プロシージャ、関数が登録される。

2番目の「SSMAExtPack-2.0.0.420.exe」をインストールすると、「sysdb」データベースの［プログラミング］-［関数］-［スカラ値関数］に、TO_CHAR、TO_DATE、LPAD、TRANC互換の関数が作成されますので、参考にすることができると思います。


SSMA関数の使い方の例としては次のような感じになります（言いかえれば、これに準ずるSQL Serverの組み込み関数は用意されていないということです）。

--■日付型から文字型へ変換[TO_CHAR]
SELECT ssma.TO_CHAR_DATE(getdate(),'yyyymmdd')
-->20080512

SELECT ssma.TO_CHAR_DATE(getdate(),'YYYY/MM/DD(DY) HH24:MI:SS〜')
-->2008/05/12(月曜日) 22:38:35〜

--■文字型から日付型へ変換[TO_DATE]
SELECT ssma.TO_DATE2('2008/01/31 13:30','YYYY/MM/DD HH24:MI')
-->2008-01-31 13:30:00.000の日付型

--■数字型から文字型へ変換[TO_CHAR]
--  文字書式(コンマ、ピリオド、'0'（必ず表示）、'9'（あれば表示）をサポート、桁あふれ時は#)
SELECT ssma.TO_CHAR_NUMERIC(12345,'9,999,990.0')
-->12,345.0

--■左詰め[LPAD]
--  帳票のブランク埋めで良く使われる
--  バイト数埋め（文字数のLPAD_nvarcharもある）
SELECT ssma.LPAD_varchar('abc',10,' ')
-->       abc(左に7個の半角スペースが付加)

--■右詰め[RPAD]
--文字数埋め（バイト数のRPAD_varcharもある）
SELECT ssma.RPAD_nvarchar('abc',10,'あ')
-->abcあああああああ

--■少数点、日付時刻の切り捨て[TRUNC]
--  四捨五入は標準関数のROUNDを使う。切り上げは無し
SELECT ssma.TRUNC(123.456,2) --少数点2桁で切捨て
-->123.45

SELECT ssma.TRUNC_DATE(getdate()) --時刻を切り捨て
-->2008-05-12 00:00:00.000

--■月末日を取得
--  時刻は現在時刻
SELECT ssma.LAST_DAY(getdate())
-->2008-05-31 22:24:03.187

その他、Oracleとの差異を吸収するためのプロシージャ（UTL_FILE_〜）や関数が用意されていますが、スカラ値関数の仕組み上「〜2」や「〜_型名」といった複数の関数に分かれていたりするので、使いにくい時もあるかもしれません。個人的には、SQL Serverの組み込み関数に慣れているので、上に書いた関数があれば十分な感じです。
ちなみに、MSDNのこのページによれば、ssma.やdbo.のようなスカラ値関数のスキーマ名は省略できないようです。