SQLのSELECT文で取得したデータを任意の形式にフォーマットして表示したいケースは多々あります。
今回はMySQLで用意されているデータフォーマット用の各種関数をご紹介していきたいと思います。
SQLのフォーマット関数: 数値
1つ目のフォーマット関数は、数値を3桁毎にカンマで区切って整形するための「FORMAT」関数をご紹介していきます。
基本構文は下記となります。
SELECT FORMAT(数値, 小数点の桁数);
第一引数にフォーマット対象の数値を指定し、第二引数に小数点として表示する桁数を指定します。
サンプル
実際に数値を指定して、FORMAT関数を使用したサンプルで具体的な動きを確認してみましょう。
7桁の数値
まずは7桁の数値で小数点無しの値にFORMAT関数を指定してみます。
サンプルでは「1234567」の数値をフォーマットし、小数点を5桁まで表示するように指定しました。
SELECT FORMAT(1234567, 5);
実行した結果が下記の通りです。
+--------------------+ | FORMAT(1234567, 5) | +--------------------+ | 1,234,567.00000 | +--------------------+
3桁毎にカンマで区切られていることをご確認頂けます。
ただし、小数点以下に関しては3桁以上でもカンマ区切りはされません。
小数点を含む数値
次は小数点を含む数値にFORMAT関数を実行してみましょう。
SELECT FORMAT(1234.567, 2);
実行した結果は下記の通りです。
+---------------------+ | FORMAT(1234.567, 2) | +---------------------+ | 1,234.57 | +---------------------+
小数点に関しては、指定した小数点の桁に四捨五入されます。
SQLのフォーマット関数: 日付
2つ目は、日付のフォーマット関数である「DATE_FORMAT」関数についてご紹介していきます。
基本構文は下記の通りです。
SELECT DATE_FORMAT(日付, 日付フォーマット);
日付フォーマットの指定で頻繁に利用するものは下記の6つです。
- %Y: 年を4桁で表示(ex:2020)
- %m: 月を2桁で表示(ex:01)
- %d: 日を2桁で表示(ex:02)
- %H: 時を2桁で表示(00~23)
- %i: 分を2桁で表示(00~59)
- %s: 秒を2桁で表示(00~59)
サンプル
では実際にサンプルで具体的なDATE_FORMAT関数の動作を確認してみましょう。
日付をフォーマット
まずは下記のように日付形式のデータを、時分秒までの日付フォーマットで指定するとどのような動きになるのか確認してみましょう。
SELECT DATE_FORMAT('2020-9-25', '%Y/%m/%d %H:%i:%s');
実行した結果が下記の通りです。
+-----------------------------------------------+
| DATE_FORMAT('2020-9-25', '%Y/%m/%d %H:%i:%s') |
+-----------------------------------------------+
| 2020/09/25 00:00:00 |
+-----------------------------------------------+
このように日付を時分秒までの表示形式にフォーマットした場合には、「00:00:00」で表示されます。
日時をフォーマット
今度は反対に日時形式のデータを年月日までの日付形式にフォーマットしてみましょう。
SELECT DATE_FORMAT('2020-9-25 14:30:21', '%Y年%m月%d日');
実行した結果が下記の通りです。
+------------------------------------------------------+
| DATE_FORMAT('2020-9-25 14:30:21', '%Y年%m月%d日') |
+------------------------------------------------------+
| 2020年09月25日 |
+------------------------------------------------------+
ちなみに区切り文字に関しては自由に設定することが出来るため、サンプルのように「年月日」と日本語で表示することも可能です。
SQLのフォーマット関数: 文字列
3つ目は文字列のフォーマット関数ですが、いくつかの関数が存在しますので代表的な関数を順にみていきましょう。
空白削除
データの前後にある空白を削除するのが「TRIM」関数です。
基本構文は下記の通りです。
SELECT TRIM(文字列);
左側の空白だけを削除する「LTRIM」関数、右側の空白だけを削除する「RTRIM」関数も存在します。
サンプル
SELECT TRIM(' テスト ');
実行すると下記のように空白が取り除かれて表示されます。
+-------------------------+
| TRIM(' テスト ') |
+-------------------------+
| テスト |
+-------------------------+
ちなみにサンプルの指定で削除出来るのは、半角の空白文字のみとなります。
全角の空白文字を削除したい場合には、先頭の全角空白は「LEADING」末尾の全角空白は「TRAILING」を指定して削除します。
SELECT TRIM(LEADING' ' FROM '文字列'); SELECT TRIM(TRAILING' ' FROM '文字列');
小文字・大文字変換
データを小文字に変換するのが「LOWER」関数、大文字に変換するのが「UPPER」関数です。
基本構文は下記の通りです。
SELECT LOWER('文字列');
SELECT UPPER('文字列');
サンプル
SELECT LOWER('ABCDE'), UPPER('fghij');
サンプルを実行した結果が下記の通りです。
+----------------+----------------+
| LOWER('ABCDE') | UPPER('fghij') |
+----------------+----------------+
| abcde | FGHIJ |
+----------------+----------------+
文字埋め
データを指定した文字数になるように、文字埋めを行うための関数が「LPAD」関数と「RPAD」関数です。
「LPAD」関数は文字列の左側を埋め、「RPAD」関数は文字列の右側を指定した文字で埋めます。
基本構文は下記の通りです。
SELECT LPAD('文字列', 桁数, '埋める文字列');
SELECT RPAD('文字列', 桁数, '埋める文字列');
サンプル
SELECT LPAD('テスト', 5, 'あ'), RPAD('abc', 5, '12345');
実行した結果が下記の通りです。
+-----------------------------+-------------------------+
| LPAD('テスト', 5, 'あ') | RPAD('abc', 5, '12345') |
+-----------------------------+-------------------------+
| ああテスト | abc12 |
+-----------------------------+-------------------------+
ちなみにサンプルのように、埋める文字列で指定した値が、指定した桁数より多くなる場合には、規定の桁数に達するまで先頭から順番に使用されます。
切り出し
データの一部分を抽出するには「SUBSTRING」関数を利用することが可能です。
基本構文は下記の通りです。
SELECT SUBSTRING('文字列', 開始位置, 抽出桁数);
サンプル
SELECT SUBSTRING('あいうえおかき', 3, 4);
実行した結果が下記の通りです。
+------------------------------------------+
| SUBSTRING('あいうえおかき', 3, 4) |
+------------------------------------------+
| うえおか |
+------------------------------------------+
連結
データを連結して1つのデータとして表示するには「CONCAT」関数を利用することが可能です。
基本構文は下記の通りです。
SELECT CONCAT('文字列1', '文字列2');
サンプル
SELECT CONCAT('ABCDE', 'あいう');
実行した結果が下記の通りです。
+------------------------------+
| CONCAT('ABCDE', 'あいう') |
+------------------------------+
| ABCDEあいう |
+------------------------------+
さいごに：SQLのフォーマット関数を活用して任意の形式に加工しよう
本記事では、SQLのフォーマット関数について、いくつか代表的な関数をご紹介してきました。
今回ご紹介した関数は、MySQL用の関数であり、データベース毎に利用可能な関数は異なります。
データの加工はJavaやPHPといったプログラム側でも実現可能ですが、SQLでの加工方法についても知識として習得しておきましょう。
