【初級編⑦】SQLの関数を使いこなす

どんなプログラム言語でも「関数」というものが存在します。Excelのそれが一番イメージしやすいかと思いますが、SQL Serverでもモチロン関数はあって、これを使いこなせるかどうかで生産性やパフォーマンスが全然違ってきます。

関数は非常に種類が多く、複雑な使い方もあるので、今回は簡単なところだけを説明します。

関数とは

数学の授業で関数って習いましたよね?一次関数とか二次関数とかのアレです。あの関数とは「ある一方の値が決まった時、もう一方の値がただ一つ決まること」だと私は習いました。SQL Serverの関数も基本的な考え方は同じです。

プログラムに特化した関数の説明をすると、「ある処理に対して、何らかのデータを入力すると、その処理結果がアウトプットされる処理」のことです。言葉だけじゃ難しいですがここで言う「何らかのデータを入力」するデータ(値)そのもののことを「引数(ひきすう)」と言い、「その結果がアウトプット」されるデータ(値)そのもののことを「戻り値(もどりち)」と言います。

関数とは

入力のことは、「引数を渡す、引数に取る」などと表現したりします。また出力のことは、「戻り値を返す、返してくる」などと表現したりします。また、引数や戻り値が無い場合もあります。

実際の例で簡単なものを挙げると、ある数値を2つ渡すとその和を返す足し算関数、などが分かりやすいと思います。

足し算関数

この時、引数の定義は「数値」であり、戻り値の定義も「数値」となります。つまり引数は数値であれば何でも良いし、戻り値も引数によって変わることになります。文字列なんかはダメという意味ですが。足し算をする処理なので当然ですね。

なぜ関数を利用するか

関数をなぜ利用するかというと、それは同じような処理を何回も行う場合に、共通の部分はテンプレート化しておき、呼び出すだけで同じ処理を実行できて便利だからです。毎回指定する値が違うだけで、足し算そのものの部分(二つの値を足す)は共通ですよね?それを毎回全てを記述するよりかは、3と5という引数を指定するだけで、処理結果が返ってくるようにした方が間違えないし、楽なんですね。

確かに足し算だけならなら記述する部分は非常に短いですが、例えば「足し算をした結果が小数点以下を含む場合、小数点第一位を自動的に四捨五入する」というような処理の場合、こういう感じになると思います。

足し算関数四捨五入付き

これを何回も書くのは面倒ですし、書いてる内に間違えてしまうかもしれないので、テストが増えます。さらに後から、「引数が数値であるかどうかチェックを追加する」なんてことになったら。。。この処理を使用している箇所全てを修正しないといけなくなります。

そこで、「引数を2つ渡したら、数値かどうかの入力チェックをしつつ和を求め、小数点第一位を四捨五入した値を返す処理」を行う関数を作っておけば、必要な時は引数を指定してその関数を呼び出すだけで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関数は、ある文字列の右端から、指定した文字数分だけを取得します。第一引数に対象の文字列、第二引数に取得したい文字数(数値)の2つの引数を取り、取得した文字列を戻り値として返します。

RIGHT関数

SELECT RIGHT('ABCDEF', 3)

------
DEF

(1 行処理されました)

RIGHT関数

(2) LEFT

LEFT関数は、ある文字列の左端から、指定した文字数分だけを取得します。第一引数に対象の文字列、第二引数に取得したい文字数(数値)の2つの引数を取り、取得した文字列を戻り値として返します。

LEFT関数

SELECT LEFT('ABCDEF', 3)

------
ABC

(1 行処理されました)

LEFT関数

(3) RTRIM

RTRIM関数は、ある文字列の右端から空白(半角スペースのみ)を取り除きます。第一引数に対象の文字列を指定します。

RTRIM関数

SELECT RTRIM('   ABC   DEF   ') + 'GHI'

------------------
   ABC   DEFGHI

(1 行処理されました)

RTRIM関数

ABCの左とDEFの左にあるスペースは取れず、右端からFまでの間にあるスペースだけが取れているのが分かります。

あくまで対象は半角スペースだけで、全角スペースは普通の文字と見なされますので注意が必要です。

(4) LTRIM

LTRIM関数は、ある文字列の左端から空白(半角スペースのみ)を取り除きます。第一引数に対象の文字列を指定します。RTRIMが右端からなのに対し、LTRIMは左端からというだけです。

LTRIM関数

全角スペースを取り除きたい場合

RTRIMとLTRIMが取り除けるのは半角スペースのみですが、それでは全角スペースを取り除くにはどうすれば良いでしょうか?RTRIM、LTRIMと同様に右端からや左端からという指定は難しいですが、ある文字列の中から全角スペースを完全に取り除くことでしたらREPLACE関数を使うと簡単にできます。

REPLACE関数については、後ろの方で詳しく説明しますが、簡単に言うと文字列の置換を行う関数です。全角スペースをブランクに置換してあげれば、全角スペースを取り除くことができます。第一引数に対象の文字列、第二引数に置換対象の文字列、第三引数に置換後の文字列を指定します。

REPLACE関数で全角スペースを取り除く

例えばchar型の列の場合、桁数が足りていない部分は半角スペースが自動的に入りますので、本来の入力値を取りたい場合はTRIMをかけて余計な部分を取り除く必要があったりします。ただ、全角スペースは基本的にユーザ入力でしか入らないので、全角スペースを取り除かないといけない要件が無ければ使う必要はないでしょう。

(5) REPLACE

REPLACE関数は、ある文字列の中から特定の文字列を別の文字列に置換する関数です。第一引数に対象の文字列、第二引数に置換したい文字列(検索文字列)、第三引数に置換後の文字列の3つの引数を取り、検索文字列を置換した後の文字列全部を戻り値として返します。

REPLACE関数

SELECT REPLACE('ABCDE', 'BCD', 'BCDBCD')

----------------------------------------
ABCDBCDE

(1 行処理されました)

REPLACE関数

(6) SUBSTRING

SUBSTRING関数は、ある文字列の中で、指定した開始位置から指定した文字数分を取得する関数です。第一引数に対象の文字列、第二引数に開始位置(何文字目か)、第三引数に取得する文字数の3つの引数をとり、取得した文字列を戻り値として返します。

SUBSTRING関数

SELECT SUBSTRING('ABCDE', 3, 2)

----
CD

(1 行処理されました)

REPLACE関数

(7) UPPER

UPPER関数は、対象の文字列(英字のみ)を大文字に変換する関数です。第一引数に対象の文字列を指定し、英字を大文字に変換した文字列を戻り値として返します。

UPPER関数

SELECT UPPER('abcあいう012')

------------------------
ABCあいう012

(1 行処理されました)

UPPER関数

(8) LOWER

UPPER関数は、対象の文字列(英字のみ)を小文字に変換する関数です。第一引数に対象の文字列を指定し、英字を小文字に変換した文字列を戻り値として返します。

LOWER関数

SELECT LOWER('ABCあいう012')

------------------------
abcあいう012

(1 行処理されました)

LOWER関数

(9) LEN

LEN関数は、対象の文字列の文字数を取得する関数です。第一引数に対象の文字列を取り、文字数を戻り値として返します。

LEN関数

SELECT LEN('ABCあいう012')

-----------
9

(1 行処理されました)

LEN関数

実際の使用例 ~日付・時刻関数~

(10) GETDATE SYSDATETIME

GETDATE関数とSYSDATETIMEは、SQL Serverがインストールされたマシンの日付を取得する関数です。引数は取らず、SQL Serverがインストールされたマシンの日付を戻り値として返します。GETDATEとSYSDATETIMEの違いは、戻り値の型がGETDATEの場合はDATETIME型で、SYSDATETIMEの場合はDATETIME2型であるだけの違いです。

GETDATE/SYSDATETIME関数

SELECT GETDATE(), SYSDATETIME()
                        
----------------------- ----------------------
2014-01-04 15:49:32.380 2014-01-04 15:49:32.38

(1 行処理されました)

GETDATE/SYSDATETIME関数

(11) YEAR

YEAR関数は、ある日付の値から年の部分だけを取得する関数です。第一引数に対象の日付値を取り、年の部分を数値型で戻り値として返します。

YEAR関数

SELECT YEAR(GETDATE())
           
-----------
       2014

(1 行処理されました)

YEAR関数

(12) MONTH

MONTH関数は、ある日付の値から月の部分だけを取得する関数です。第一引数に対象の日付値を取り、月の部分を数値型で戻り値として返します。

MONTH関数

SELECT MONTH(GETDATE())
           
-----------
          1

(1 行処理されました)

MONTH関数

(13) DAY

DAY関数は、ある日付の値から日の部分だけを取得する関数です。第一引数に対象の日付値を取り、日の部分を数値型で戻り値として返します。

DAY関数

SELECT DAY(GETDATE())
           
-----------
          4

(1 行処理されました)

DAY関数

(14) DATEPART

DATEPART関数は、YEAR関数とMONTH関数とDAY関数を纏めたような関数で、ある日付の値から取得したい日付の種類を指定すれば、それに対応する日付の値が取得できる関数です。第一引数に日付の種類、第二引数に対象の日付の2つを引数として取り、結果の数値を戻り値として返します。

DATEPART関数

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 行処理されました)

DATEPART関数

参考として、日付の種類の一覧を示します。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関数は、日付値の加算や減算を行う関数で、よく使います。第一引数に加算を行う単位(日付の種類)、第二引数に加算値、第三引数に加算対象の日付の3つの引数をとり、計算した結果の日付を戻り値として返します。

マイナスの値を加算することで、減算(過去に戻す)ことも可能です。指定できる日付の種類は、DATEPART関数のところで載せた表と同じものが使用できます。

DATEADD関数

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 行処理されました)

DATEADD関数

(16) DATEDIFF

DATEDIFF関数は、2つの日付の差を求める関数で、これも結構重要です。第一引数に加算を行う単位(日付の種類)、第二引数に加算値、第三引数に加算対象の日付の3つの引数をとり、計算した結果の日付を戻り値として返します。

DATEDIFF関数

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 行処理されました)

DATEDIFF関数






2-4 実際の使用例 ~変換関数~

(17) CAST

CAST関数は、あ値を別の型に変換する関数です。第一引数に変換対象の値を記述し、カンマでは区切らず、続けて「AS 変換後の型」と書きます。変換後の値が戻り値として返ります。

CAST関数

SELECT CAST('012' AS INT)
	  ,CAST(12 AS VARCHAR)
            
----------- ------------------------------
12          12

(1 行処理されました)

CAST関数

次のように、文字列を繋ぎ合わせて日付に変換することも可能です。

日付のCAST

CAST関数はSQL標準の関数なので別のDBでも使用できますが、SQL Server専用の「CONVERT」という関数があります。他のDBに移植、もしくはコードを共有する場合は「CAST」の使用を検討すべきですが、SQL Serverだけで良いなら次に説明する「CONVERT」関数を使った方がいいです。

(18) CONVERT

CONVERT関数は、CAST関数と同様に対象の値を指定したデータ型へ変換する関数です。CASTよりこちらの方が便利です。第一引数に変換後のデータ型、第二引数に変換対象の値の2つの引数を取り、変換後の値を戻り値として返します。

また、第三引数に書式(スタイル)を指定することで、色々な書式で出力することができますが、特に重要なのは日付だけで、それ以外は基本的に使う場面は少ないでしょう。

CONVERT関数

SELECT CONVERT(INT, '012')
	  ,CONVERT(VARCHAR, 12)
            
----------- ------------------------------
12          12

(1 行処理されました)

CONVERT関数

日付を文字列に変換する時には、第三引数に書式を指定することで、日付値を色々な文字列に変換できます。パターンは沢山あるので、使いそうなものを幾つか挙げました。

日付を文字列にCONVERT

書式(スタイル) 表示形式
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つを引数に取ります。丸めた結果の数値を戻り値として返します。

有効桁数は、正の値を指定した場合は小数点以下の桁数に、負の値を指定した場合は、小数点から左の桁数にまで丸めます。例えば、小数点第三位を四捨五入して第二位まで求める場合は、有効桁数に2を指定します。

ROUND関数

SELECT ROUND(123.445, 2)
	  ,ROUND(123.445, -2)
	  ,ROUND(153.445, -2)
                                                                                
------------------------ ------------------------ ------------------------
123.450                  100.000                  200.000

(1 行処理されました)

ROUND関数

ROUND関数の有効桁数

(20) POWER

POWER関数は、階乗を計算する関数です。第一引数に対象の

POWER関数

SELECT POWER(2, 3)

-----------
8

(1 行処理されました)

POWER関数

(21) RAND

RAND関数は、0から1までの範囲でランダムな数値を求める関数です。引数は指定できますが複雑なので指定しないでOKです。0から1までの範囲のランダムな数値を戻り値として返します。

RAND関数

SELECT RAND(), RAND(), RAND()
                                              
---------------------- ---------------------- ----------------------
0.273604264024087      0.949061768386125      0.880059316061561

(1 行処理されました)

RAND関数

(22) CEILING

CEILING関数は、小数点以下を切り上げる関数です。厳密に言うと「その値と等しいかそれより大きい最小の整数を求める」です。第一引数に対象の値を取り、計算結果を戻り値として返します。

CEILING関数

SELECT CEILING(1)
	  ,CEILING(1.1)
	  ,CEILING(1.9)

----------- ------------------------ ------------------------
1           2                        2

(1 行処理されました)

CEILING関数

(23) FLOOR

FLOOR関数は、小数点以下を切り捨てる関数です。厳密に言うと「その値と等しいかそれより小さい最大の整数を求める」です。第一引数に対象の値を取り、計算結果を戻り値として返します。

FLOOR関数

SELECT FLOOR(1)
	  ,FLOOR(1.1)
	  ,FLOOR(2.1)

----------- ------------------------ ------------------------
1           1                        2

(1 行処理されました)

FLOOR関数

(24) SQRT

SQRT関数は、平方根を求める関数です。第一引数に対象の値を取り、その平方根を戻り値として返します。

SQRT関数

SELECT SQRT(2)

----------------------
1.4142135623731

(1 行処理されました)

SQRT関数

WHERE句でも関数は使用できます

これまで関数の説明をしてきましたが、SELECT句だけでなくWHERE句でも関数は使用できます。SELECT句に書く場合と同じように記述すればOKです。

社員テーブルを使って試してみると、例えば名前(名字含む)の2文字目が「中」の社員を抜き出したい場合、「名前の2文字目から1文字取得し、それが中と等しいかどうか」という条件を指定するとします。

社員テーブル全件

SELECT *
FROM Table_Syain
WHERE SUBSTRING(名前, 2, 1) = '中'

社員番号  名前                            年齢 性別   生年月日                給料
----- -------------------- ----------- ---- ---------- -----------
00001 田中 一郎                         20 男    1993-01-01      205000

(1 行処理されました)

名前の2文字目から1文字取り出した値が中と等しいか

また、姓と名の間の空白を含めて名前の文字数が6文字以上ある社員を抜き出すにはこのように記述します。

SELECT *
FROM Table_Syain
WHERE LEN(名前) >= 6
社員番号  名前                            年齢 性別   生年月日                給料
----- -------------------- ----------- ---- ---------- -----------
00003 鈴木 三つ子                        28 女    1985-01-03      250000

(1 行処理されました)

名前が6文字以上の社員を抽出

この記事と関連性の高い記事