どんなプログラム言語でも「関数」というものが存在します。Excelのそれが一番イメージしやすいかと思いますが、SQL Serverでもモチロン関数はあって、これを使いこなせるかどうかで生産性やパフォーマンスが全然違ってきます。
関数は非常に種類が多く、複雑な使い方もあるので、今回は簡単なところだけを説明します。
目次
関数とは
数学の授業で関数って習いましたよね？一次関数とか二次関数とかのアレです。あの関数とは「ある一方の値が決まった時、もう一方の値がただ一つ決まること」だと私は習いました。SQL Serverの関数も基本的な考え方は同じです。
プログラムに特化した関数の説明をすると、「ある処理に対して、何らかのデータを入力すると、その処理結果がアウトプットされる処理」のことです。言葉だけじゃ難しいですがここで言う「何らかのデータを入力」するデータ（値）そのもののことを「引数（ひきすう）」と言い、「その結果がアウトプット」されるデータ（値）そのもののことを「戻り値（もどりち）」と言います。
入力のことは、「引数を渡す、引数に取る」などと表現したりします。また出力のことは、「戻り値を返す、返してくる」などと表現したりします。また、引数や戻り値が無い場合もあります。
実際の例で簡単なものを挙げると、ある数値を２つ渡すとその和を返す足し算関数、などが分かりやすいと思います。
この時、引数の定義は「数値」であり、戻り値の定義も「数値」となります。つまり引数は数値であれば何でも良いし、戻り値も引数によって変わることになります。文字列なんかはダメという意味ですが。足し算をする処理なので当然ですね。
なぜ関数を利用するか
関数をなぜ利用するかというと、それは同じような処理を何回も行う場合に、共通の部分はテンプレート化しておき、呼び出すだけで同じ処理を実行できて便利だからです。毎回指定する値が違うだけで、足し算そのものの部分（二つの値を足す）は共通ですよね？それを毎回全てを記述するよりかは、3と5という引数を指定するだけで、処理結果が返ってくるようにした方が間違えないし、楽なんですね。
確かに足し算だけならなら記述する部分は非常に短いですが、例えば「足し算をした結果が小数点以下を含む場合、小数点第一位を自動的に四捨五入する」というような処理の場合、こういう感じになると思います。
これを何回も書くのは面倒ですし、書いてる内に間違えてしまうかもしれないので、テストが増えます。さらに後から、「引数が数値であるかどうかチェックを追加する」なんてことになったら。。。この処理を使用している箇所全てを修正しないといけなくなります。
そこで、「引数を２つ渡したら、数値かどうかの入力チェックをしつつ和を求め、小数点第一位を四捨五入した値を返す処理」を行う関数を作っておけば、必要な時は引数を指定してその関数を呼び出すだけでOKになります。コードを書く量がグッと減ります。
関数の処理が正しく動いていれば、呼び出した時によって四捨五入の仕方が間違うこともないですし、処理を追加する場合も関数の中身を一カ所変えるだけで全て変わってくれます。
関数の使い方
前置きが長くなりましたが、SQL Serverでの関数の使い方を説明します。関数はSQLの中で色々な場所で記述できますが、まずは「SELECT句の中」で書くことを中心に説明を進めて行きます。関数を呼び出すには、関数名を記述し後ろに半角のカッコ()を書きます。引数は()の中に記述します。
引数が複数ある場合、左から順番に「第一引数」「第二引数」「第三引数」と呼びます。
よく使う関数一覧
よく使うSQLServerの関数を表に纏めました。この表にあるNo1～No24まで、その使用方法を説明します。
| No | 種類 | 関数名 | 説明 |
|---|---|---|---|
| 1 | 文字列 | RIGHT | ある文字列の右端から、指定した文字数分だけ取得する |
| 2 | LEFT | ある文字列の左端から、指定した文字数分だけ取得する | |
| 3 | RTRIM | ある文字列の右端から空白を取り除く | |
| 4 | LTRIM | ある文字列の左端から空白を取り除く | |
| 5 | REPLACE | ある文字列の中で、特定の文字列を別の文字列に置換する | |
| 6 | SUBSTRING | ある文字列の中で、特定の位置から指定した文字数分だけ取得する | |
| 7 | UPPER | ある文字列を大文字に変換する | |
| 8 | LOWER | ある文字列を小文字に変換する | |
| 9 | LEN | ある文字列の文字数を取得する | |
| 10 | 日付・時刻 | GETDATE SYSDATETIME |
システム日付(SQL Serverがインストールされたマシンの日付)を取得する |
| 11 | YEAR | ある日付の年の部分だけ取得する | |
| 12 | MONTH | ある日付の月の部分だけ取得する | |
| 13 | DAY | ある日付の日の部分だけ取得する | |
| 14 | DATEPART | ある日付の一部だけを取得する | |
| 15 | DATEADD | 日付を加算したり減算したりする | |
| 16 | DATEDIFF | 2つの日付の差を求める | |
| 17 | 変換 | CAST | データ型を変換する |
| 18 | CONVERT | データ型を変換する | |
| 19 | 数値 | ROUND | 四捨五入する |
| 20 | POWER | 階乗を求める | |
| 21 | RAND | 0から1までの範囲のランダムな数字を求める | |
| 22 | CEILING | 小数点以下を切り上げる | |
| 23 | FLOOR | 小数点以下を切り捨てる | |
| 24 | SQRT | 平方根を求める |
実際の使用例 ～文字列関数～
(1) RIGHT
RIGHT関数は、ある文字列の右端から、指定した文字数分だけを取得します。第一引数に対象の文字列、第二引数に取得したい文字数（数値）の２つの引数を取り、取得した文字列を戻り値として返します。
SELECT RIGHT('ABCDEF', 3)
------
DEF
(1 行処理されました)
(2) LEFT
LEFT関数は、ある文字列の左端から、指定した文字数分だけを取得します。第一引数に対象の文字列、第二引数に取得したい文字数（数値）の２つの引数を取り、取得した文字列を戻り値として返します。
SELECT LEFT('ABCDEF', 3)
------
ABC
(1 行処理されました)
(3) RTRIM
RTRIM関数は、ある文字列の右端から空白(半角スペースのみ)を取り除きます。第一引数に対象の文字列を指定します。
SELECT RTRIM(' ABC DEF ') + 'GHI'
------------------
ABC DEFGHI
(1 行処理されました)
ABCの左とDEFの左にあるスペースは取れず、右端からFまでの間にあるスペースだけが取れているのが分かります。
あくまで対象は半角スペースだけで、全角スペースは普通の文字と見なされますので注意が必要です。
(4) LTRIM
LTRIM関数は、ある文字列の左端から空白(半角スペースのみ)を取り除きます。第一引数に対象の文字列を指定します。RTRIMが右端からなのに対し、LTRIMは左端からというだけです。
RTRIMとLTRIMが取り除けるのは半角スペースのみですが、それでは全角スペースを取り除くにはどうすれば良いでしょうか？RTRIM、LTRIMと同様に右端からや左端からという指定は難しいですが、ある文字列の中から全角スペースを完全に取り除くことでしたらREPLACE関数を使うと簡単にできます。
REPLACE関数については、後ろの方で詳しく説明しますが、簡単に言うと文字列の置換を行う関数です。全角スペースをブランクに置換してあげれば、全角スペースを取り除くことができます。第一引数に対象の文字列、第二引数に置換対象の文字列、第三引数に置換後の文字列を指定します。
例えばchar型の列の場合、桁数が足りていない部分は半角スペースが自動的に入りますので、本来の入力値を取りたい場合はTRIMをかけて余計な部分を取り除く必要があったりします。ただ、全角スペースは基本的にユーザ入力でしか入らないので、全角スペースを取り除かないといけない要件が無ければ使う必要はないでしょう。
(5) REPLACE
REPLACE関数は、ある文字列の中から特定の文字列を別の文字列に置換する関数です。第一引数に対象の文字列、第二引数に置換したい文字列(検索文字列)、第三引数に置換後の文字列の３つの引数を取り、検索文字列を置換した後の文字列全部を戻り値として返します。
SELECT REPLACE('ABCDE', 'BCD', 'BCDBCD')
----------------------------------------
ABCDBCDE
(1 行処理されました)
(6) SUBSTRING
SUBSTRING関数は、ある文字列の中で、指定した開始位置から指定した文字数分を取得する関数です。第一引数に対象の文字列、第二引数に開始位置（何文字目か）、第三引数に取得する文字数の３つの引数をとり、取得した文字列を戻り値として返します。
SELECT SUBSTRING('ABCDE', 3, 2)
----
CD
(1 行処理されました)
(7) UPPER
UPPER関数は、対象の文字列（英字のみ）を大文字に変換する関数です。第一引数に対象の文字列を指定し、英字を大文字に変換した文字列を戻り値として返します。
SELECT UPPER('abcあいう012')
------------------------
ABCあいう012
(1 行処理されました)
(8) LOWER
UPPER関数は、対象の文字列（英字のみ）を小文字に変換する関数です。第一引数に対象の文字列を指定し、英字を小文字に変換した文字列を戻り値として返します。
SELECT LOWER('ABCあいう012')
------------------------
abcあいう012
(1 行処理されました)
(9) LEN
LEN関数は、対象の文字列の文字数を取得する関数です。第一引数に対象の文字列を取り、文字数を戻り値として返します。
SELECT LEN('ABCあいう012')
-----------
9
(1 行処理されました)
実際の使用例 ～日付・時刻関数～
(10) GETDATE SYSDATETIME
GETDATE関数とSYSDATETIMEは、SQL Serverがインストールされたマシンの日付を取得する関数です。引数は取らず、SQL Serverがインストールされたマシンの日付を戻り値として返します。GETDATEとSYSDATETIMEの違いは、戻り値の型がGETDATEの場合はDATETIME型で、SYSDATETIMEの場合はDATETIME2型であるだけの違いです。
SELECT GETDATE(), SYSDATETIME()
----------------------- ----------------------
2014-01-04 15:49:32.380 2014-01-04 15:49:32.38
(1 行処理されました)
(11) YEAR
YEAR関数は、ある日付の値から年の部分だけを取得する関数です。第一引数に対象の日付値を取り、年の部分を数値型で戻り値として返します。
SELECT YEAR(GETDATE())
-----------
2014
(1 行処理されました)
(12) MONTH
MONTH関数は、ある日付の値から月の部分だけを取得する関数です。第一引数に対象の日付値を取り、月の部分を数値型で戻り値として返します。
SELECT MONTH(GETDATE())
-----------
1
(1 行処理されました)
(13) DAY
DAY関数は、ある日付の値から日の部分だけを取得する関数です。第一引数に対象の日付値を取り、日の部分を数値型で戻り値として返します。
SELECT DAY(GETDATE())
-----------
4
(1 行処理されました)
(14) DATEPART
DATEPART関数は、YEAR関数とMONTH関数とDAY関数を纏めたような関数で、ある日付の値から取得したい日付の種類を指定すれば、それに対応する日付の値が取得できる関数です。第一引数に日付の種類、第二引数に対象の日付の２つを引数として取り、結果の数値を戻り値として返します。
SELECT DATEPART(YEAR , GETDATE()) AS 年
,DATEPART(MONTH , GETDATE()) AS 月
,DATEPART(DAY , GETDATE()) AS 日
,DATEPART(HOUR , GETDATE()) AS 時
,DATEPART(MINUTE , GETDATE()) AS 分
,DATEPART(SECOND , GETDATE()) AS 秒
,DATEPART(MILLISECOND , GETDATE()) AS ﾐﾘ秒
,DATEPART(NANOSECOND , GETDATE()) AS ﾅﾉ秒
年 月 日 時 分 秒 ﾐﾘ秒 ﾅﾉ秒
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2014 1 4 16 34 52 40 40000000
(1 行処理されました)
参考として、日付の種類の一覧を示します。SQLで記述する際は、左側の正式名称でも、右側の省略形でもどちらでも可能です。
| 日付の種類 | 省略形 |
|---|---|
| year | yy ,yyyy |
| quarter | qq ,q |
| month | mm , m |
| dayofyear | dy ,y |
| day | dd ,d |
| week | wk ,ww |
| weekday | dw |
| hour | hh |
| minute | mi, n |
| second | ss , s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
(15) DATEADD
DATEADD関数は、日付値の加算や減算を行う関数で、よく使います。第一引数に加算を行う単位(日付の種類)、第二引数に加算値、第三引数に加算対象の日付の３つの引数をとり、計算した結果の日付を戻り値として返します。
マイナスの値を加算することで、減算（過去に戻す）ことも可能です。指定できる日付の種類は、DATEPART関数のところで載せた表と同じものが使用できます。
SELECT DATEADD(d, 1, '2014/01/01')
,DATEADD(d, -1, '2014/01/01')
----------------------- -----------------------
2014-01-02 00:00:00.000 2013-12-31 00:00:00.000
(1 行処理されました)
(16) DATEDIFF
DATEDIFF関数は、2つの日付の差を求める関数で、これも結構重要です。第一引数に加算を行う単位(日付の種類)、第二引数に加算値、第三引数に加算対象の日付の３つの引数をとり、計算した結果の日付を戻り値として返します。
SELECT DATEDIFF(d, '2014/01/01', '2014/01/01')
,DATEDIFF(d, '2014/01/01', '2014/01/02')
,DATEDIFF(d, '2014/01/02', '2014/01/01')
----------- ----------- -----------
0 1 -1
(1 行処理されました)
2-4 実際の使用例 ～変換関数～
(17) CAST
CAST関数は、あ値を別の型に変換する関数です。第一引数に変換対象の値を記述し、カンマでは区切らず、続けて「AS 変換後の型」と書きます。変換後の値が戻り値として返ります。
SELECT CAST('012' AS INT)
,CAST(12 AS VARCHAR)
----------- ------------------------------
12 12
(1 行処理されました)
次のように、文字列を繋ぎ合わせて日付に変換することも可能です。
CAST関数はSQL標準の関数なので別のDBでも使用できますが、SQL Server専用の「CONVERT」という関数があります。他のDBに移植、もしくはコードを共有する場合は「CAST」の使用を検討すべきですが、SQL Serverだけで良いなら次に説明する「CONVERT」関数を使った方がいいです。
(18) CONVERT
CONVERT関数は、CAST関数と同様に対象の値を指定したデータ型へ変換する関数です。CASTよりこちらの方が便利です。第一引数に変換後のデータ型、第二引数に変換対象の値の２つの引数を取り、変換後の値を戻り値として返します。
また、第三引数に書式(スタイル)を指定することで、色々な書式で出力することができますが、特に重要なのは日付だけで、それ以外は基本的に使う場面は少ないでしょう。
SELECT CONVERT(INT, '012')
,CONVERT(VARCHAR, 12)
----------- ------------------------------
12 12
(1 行処理されました)
日付を文字列に変換する時には、第三引数に書式を指定することで、日付値を色々な文字列に変換できます。パターンは沢山あるので、使いそうなものを幾つか挙げました。
| 書式(スタイル) | 表示形式 |
|---|---|
| 111 | 年/月/日(yyyy/mm/dd) |
| 112 | 年月日(yyyymmdd) |
| 120 | 年-月-日 時:分:秒(yyyy-mm-dd hh:mi:ss) |
| 121 | 年-月-日 時:分:秒.ミリ秒(yyyy-mm-dd hh:mi:ss.mmm) |
2-5 実際の使用例 ～数値関数～
(19) ROUND
ROUND関数は算術丸め(四捨五入)をする関数です。第一引数に対象の値、第二引数に有効桁数の２つを引数に取ります。丸めた結果の数値を戻り値として返します。
有効桁数は、正の値を指定した場合は小数点以下の桁数に、負の値を指定した場合は、小数点から左の桁数にまで丸めます。例えば、小数点第三位を四捨五入して第二位まで求める場合は、有効桁数に2を指定します。
SELECT ROUND(123.445, 2)
,ROUND(123.445, -2)
,ROUND(153.445, -2)
------------------------ ------------------------ ------------------------
123.450 100.000 200.000
(1 行処理されました)
(20) POWER
POWER関数は、階乗を計算する関数です。第一引数に対象の
SELECT POWER(2, 3) ----------- 8 (1 行処理されました)
(21) RAND
RAND関数は、0から1までの範囲でランダムな数値を求める関数です。引数は指定できますが複雑なので指定しないでOKです。0から1までの範囲のランダムな数値を戻り値として返します。
SELECT RAND(), RAND(), RAND()
---------------------- ---------------------- ----------------------
0.273604264024087 0.949061768386125 0.880059316061561
(1 行処理されました)
(22) CEILING
CEILING関数は、小数点以下を切り上げる関数です。厳密に言うと「その値と等しいかそれより大きい最小の整数を求める」です。第一引数に対象の値を取り、計算結果を戻り値として返します。
SELECT CEILING(1) ,CEILING(1.1) ,CEILING(1.9) ----------- ------------------------ ------------------------ 1 2 2 (1 行処理されました)
(23) FLOOR
FLOOR関数は、小数点以下を切り捨てる関数です。厳密に言うと「その値と等しいかそれより小さい最大の整数を求める」です。第一引数に対象の値を取り、計算結果を戻り値として返します。
SELECT FLOOR(1) ,FLOOR(1.1) ,FLOOR(2.1) ----------- ------------------------ ------------------------ 1 1 2 (1 行処理されました)
(24) SQRT
SQRT関数は、平方根を求める関数です。第一引数に対象の値を取り、その平方根を戻り値として返します。
SELECT SQRT(2) ---------------------- 1.4142135623731 (1 行処理されました)
WHERE句でも関数は使用できます
これまで関数の説明をしてきましたが、SELECT句だけでなくWHERE句でも関数は使用できます。SELECT句に書く場合と同じように記述すればOKです。
社員テーブルを使って試してみると、例えば名前(名字含む)の2文字目が「中」の社員を抜き出したい場合、「名前の2文字目から1文字取得し、それが中と等しいかどうか」という条件を指定するとします。
SELECT * FROM Table_Syain WHERE SUBSTRING(名前, 2, 1) = '中' 社員番号 名前 年齢 性別 生年月日 給料 ----- -------------------- ----------- ---- ---------- ----------- 00001 田中 一郎 20 男 1993-01-01 205000 (1 行処理されました)
また、姓と名の間の空白を含めて名前の文字数が6文字以上ある社員を抜き出すにはこのように記述します。
SELECT * FROM Table_Syain WHERE LEN(名前) >= 6 社員番号 名前 年齢 性別 生年月日 給料 ----- -------------------- ----------- ---- ---------- ----------- 00003 鈴木 三つ子 28 女 1985-01-03 250000 (1 行処理されました)
(13) DAYの説明分の記述が、「月の部分は、数値で４」となっていました。