本気時では、日付やNULLを扱う関数や変換関数を使用してみます。

動作確認は SQL Server 2005 Developer Edition で行いました。T-SQLで使用できる関数に関してオンラインのリンクを掲載しておきます。

関数 (Transact-SQL) 
http://msdn.microsoft.com/ja-jp/library/ms174318.aspx

 

1. 日付関数

日付を取り扱う関数の使用サンプルを掲載します。現在日時を求めるにはGETDATE(), CURRENT_TIMESTAMP, GETUTCDATE() などを使用します。日時を足すにはDATEADD 関数。年月日曜日、時間の特定の値を整数値として使用するにはDATEPART(), YEAR() 関数などを使用します。

入力された文字列が日付フォーマット化はISDATE()関数を使用します。

-- 現在日付を取得する,GETUTCDATE()はUTC時間を返す
SELECT GETDATE(), CURRENT_TIMESTAMP, GETUTCDATE()

-- 日時を加える
-- 第一引数(datepart)にはyy,qq,mmなど加算する
-- 単位を指定可能。詳細はBOOKS ONLINE参照
SELECT DATEADD(dd, 1, GETDATE())

-- 日時の時差を表示する
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET @Start = GETDATE()
SET @End = DATEADD(dd, 1, GETDATE())
SELECT DATEDIFF(d, @Start, @End)

-- 日時、曜日の名前を取得する
SELECT DATENAME( dw, GETDATE())

-- 日時、曜日で指定された年月日曜日の数値を取得する
SELECT DATEPART(dd, GETDATE()) -- 日付(12など)
SELECT DATEPART(yy, GETDATE()) -- 年(2009など)

-- DATEPARTと同様に、年月日を抜き出して
-- 表示する
SELECT DAY(GETDATE())
SELECT MONTH(GETDATE())
SELECT YEAR(GETDATE())

-- 日付かを調べる
SELECT ISDATE('2005/4/2')
SELECT ISDATE('4/2/2005')

2. NULLを扱う関数

NULLを扱う関数のサンプルを掲載します。IS NULL はWHERE句やIF文などで値がNULL蚊を調べるのに使用します。ISNULLは第一引数がNULLの場合第二引数を値とする関数で、NULLIFは第一、第二引数が等しい場合にNULLとなる関数です。COALESCEは引数のなかで最初にNULLでない値を返します。

-- 値がNULLかを調べる
DECLARE @test int
SET @test = NULL
IF @test IS NULL
 PRINT '@test is NULL'
IF @test IS NOT NULL
 PRINT '@test is not NULL'

-- 引数1,2が等しい場合はNULL
-- 等しくない場合は第一引数が返る
SELECT NULLIF(1,1)
SELECT NULLIF(1,2)

-- 第一引数がNULLの場合は第二引数が値となる
-- そうでない場合は第一引数が値となる
SELECT ISNULL(NULL, '3')

-- 引数の中から、NULL でない最初の式を返す
SELECT COALESCE(NULL,NULL,2,NULL)

3.変換関数

データ型の変換にはCAST()関数とCONVERT関数を使用します。 DateTime型を特定のフォーマットの文字列とするにはCONVERTにstyleを指定します。下記サンプルでもyyyy/mm/dd形式に変換するサンプルを掲載しています。指定可能なスタイルの種類はBooks Online を参照願います。

-- yyyy/mm/dd形式に変換
SELECT CONVERT(varchar(10),GETDATE(),  111)
-- yyyymmdd形式に変換
SELECT CONVERT(varchar(10),GETDATE(),  112)

-- XML型にキャスト
SELECT CAST ('<test>hello</test>' AS XML)

4.そのほか

そのほか便利そうな関数を掲載します。引数で指定された文字列が数値かを調べるにはISNUMERIC()関数を使用します。QUOTENAME()関数を使用するとSQL Server の識別子を表すように[]で文字列が囲まれ、文字列中の[や]はエスケープされた文字列が返されます。DATALENGTHは指定された式を表すのに必要なバイト数を返します。

-- 入力文字列から区切り記号で囲まれた有効な
--  Microsoft SQL Server 2005 識別子を作成します。
SELECT QUOTENAME(N't[]est') -- [t[]]est] となる
-- 引数が数値化を判定します
SELECT ISNUMERIC('12')
SELECT DATALENGTH(N'test')  -- 結果は8

5.まとめ

今回の説明は以上です。SQL Server では、さまざまな関数が用意されています。ここですべてを紹介できませんが、Books Onlineを調べるといろいろ新しい発見があると思います。

5.1 文字列関数

文字列関数を調べていたらいくつか知らないものがあったので、関数名と機能説明の表をメモとして作成しておきます。説明自体はBOOKS Onlineの内容です。関数一覧からリンクをクリックしないと説明が表示されないので一覧として確認できるように表に含めました。

関数名 機能
ASCII 文字式の左端の文字の ASCII コード値を返します。
CHAR ASCII コードを文字に変換します。
CHARINDEX 指定された式の文字列の中での開始位置を返します。
PATINDEX すべて有効なテキスト型と文字型で指定された式の中で、パターンが最初に現れる先頭位置を返します。パターンが見つからない場合は、0 を返します。
LEFT 文字列の左端から指定された数の文字を返します。
RIGHT 文字列の右端から指定された数の文字を返します。
LEN 指定した文字列式の、末尾の空白を除いた文字数を返します。バイト数ではありません。
LOWER 大文字が小文字に変換された状態の文字式を返します。
UPPER 小文字データを大文字に変換して文字式を返します。
LTRIM 先頭の空白を削除した後の文字式を返します。 
RTRIM 後続するすべての空白を切り捨てた後の文字列を返します。
NCHAR Unicode 標準の定義に従って、指定された整数コードの Unicode 文字を返します。
UNICODE Unicode 標準に定義された、入力式の先頭文字の整数値を返します。
QUOTENAME Unicode 文字列に区切り記号を追加して返すことで、入力文字列から区切り記号で囲まれた有効な Microsoft SQL Server 2005 識別子を作成します。
REPLACE

1 番目に指定した文字列式の中から、2 番目に指定した文字列のすべてを、3 番目に指定した文字列で置き換えます。
REPLICATE 文字式を指定した回数繰り返します。
REVERSE 文字式を逆に並べ替えたものを返します。
SPACE 連続する空白文字で構成される文字列を返します。
STR 数値データから変換された文字データを返します。
STUFF 指定した長さ分の文字を削除し、指定した開始位置に別の文字列を挿入します。 
SUBSTRING 文字、バイナリ、テキスト、またはイメージ型の式の一部を返します。

5.2 集計関数

同様に集計関数を表にまとめました。

AVG  グループ内の値の平均を返します。NULL 値は無視されます。
CHECKSUM  テーブルの 1 つの行、または一連の式に対して計算されたチェックサム値を返します。CHECKSUM は、ハッシュ インデックスの作成に使用します。
CHECKSUM_AGG グループ内にある値のチェックサムを返します。NULL 値は無視されます。
COUNT グループ内のアイテム数を返します。COUNT_BIG と COUNT 関数の違いは、戻り値のデータ型だけです。COUNT は常に int 型の値を返します。COUNT_BIG は常に bigint 型の値を返します。
COUNT_BIG

グループ内の項目数を返します。COUNT_BIG は COUNT 関数と同じように動作します。2 つの関数の違いは、戻り値のデータ型だけです。COUNT_BIG は常に bigint 型の値を返します。COUNT は常に int 型の値を返します。
GROUPING

追加された列を出力する集計関数です。CUBE または ROLLUP 演算子によって行を追加するときは値 1、行が CUBE と ROLLUP のどちらの結果でもないときは値 0 がそれぞれ使用されます。
MAX 式の最大値を返します。
MIN 式内の最小値を返します。
SUM 式の、すべての値または DISTINCT 値のみの合計を返します。SUM は、数値型列に対して使用できます。NULL 値は無視されます。
STDEV 指定された式にあるすべての値の統計的標準偏差を返します
STDEVP 指定された式のすべての値を母集団として標準偏差を返します。
VAR 指定された式のすべての値の統計的変位を返します。
VARP 指定した式のすべての値について、母集団に対する統計的変位を返します。

間違、指摘点などがありましたらご連絡ください。では。