SQLの数値を文字列に変換する方法についてまとめています。
SQLの数値を文字列に変換する関数
SQLの数値を文字列に変換するには、変換用の関数を利用します。日付を表す数値を、人間にわかりやすい日付文字列に変換したり、桁数の多い数値をカンマ編集したりゼロ詰めする際に使われます。
変換用の関数は、SQLの規格で定義されていないため、DBMSによって関数名に違いがあります。
Oracleで数値を文字列に変換するにはTO_CHAR
例えば、OracleではTO_CHARという関数が用意されています。
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL 結果 ---------------- 2020/08/20 12:00:00
【関連記事】
▶TO_CHAR関数の使い方を解説！SQLで数値や日付を書式を指定して文字列に変換
TO_CHARは、日付書式への変換以外にカンマ編集やゼロ詰めにも使えます。
MySQLではCONVERTやDATE_FORMAT、LPADを使用
Oracleと違って、MySQLでは数値を文字列変換するのに複数の関数を組み合わせる必要があります。
例えば、日付を表す数値を文字列に変換するにはCONVERTを使います。
mysql> SELECT CONVERT(20201013, DATE); +-------------------------+ | CONVERT(20201013, DATE) | +-------------------------+ | 2020-10-13 | +-------------------------+ 1 row in set (0.00 sec)
上記は、数値20201013がハイフン付きの文字列2020-10-13に変換される例です。
【関連記事】
▶SQLでデータ型を変換する方法とは！？CAST/CONVERT関数の使い方
また、date型のデータをフォーマット文字列に従って文字列に変換するためのdate_format関数が用意されてます。
// 現在の日付・時刻 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-04-17 02:29:05 | +---------------------+ // now()をYYYY-MM-DD形式に変換 mysql> select date_format(now(),'%Y-%m-%d' ); +--------------------------------+ | date_format(now(),'%Y-%m-%d' ) | +--------------------------------+ | 2020-04-17 | +--------------------------------+ // now()の日付部分のみ取得 mysql> select date(now()); +-------------+ | date(now()) | +-------------+ | 2020-04-17 | +-------------+ // now()の年部分のみ取得 mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2020 | +-------------+
‘%Y-%m-%d’がフォーマット文字列で、%Yは4桁の西暦年、%mは月、%dは日を表します。
【関連記事】
▶SQLのdateは、日付を扱うデータ型 書式変換、加減算、比較が可能 – 働くエンジニアマガジン「ポテパンスタイル」
数値を指定した桁でゼロ埋めするには、lpad関数を使用します。
// 4桁の0埋め mysql> select lpad(1, 4, '0'); +-----------------+ | lpad(1, 4, '0') | +-----------------+ | 0001 | +-----------------+ 1 row in set (0.00 sec) //2桁の数字を4桁の0埋め mysql> select lpad(99, 4, '0'); +------------------+ | lpad(99, 4, '0') | +------------------+ | 0099 | +------------------+ 1 row in set (0.00 sec) //4桁の数字を4桁の0埋め （そのまま出力される） mysql> select lpad(1234, 4, '0'); +--------------------+ | lpad(1234, 4, '0') | +--------------------+ | 1234 | +--------------------+ 1 row in set (0.00 sec)
例えば4桁のゼロ埋めとは、1→0001、99→0099、1234→1234となります。lpad関数はゼロだけではなく、指定した文字で桁を埋めることが可能です。MySQLでは、テーブルの属性にZEROFILLを指定することで、変換関数なしで自動的にゼロ埋めすることができます。
mysql> CREATE TABLE `demo`( `id` int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `num` int(3) ZEROFILL NOT NULL); Query OK, 0 rows affected, 3 warnings (0.28 sec) mysql> INSERT INTO `demo`(`num`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `demo`(`num`) VALUES (20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `demo`(`num`) VALUES (999); Query OK, 1 row affected (0.01 sec) mysql> select * from demo; +----+-----+ | id | num | +----+-----+ | 1 | 001 | | 2 | 020 | | 3 | 999 | +----+-----+ 3 rows in set (0.00 sec)
【関連記事】
▶SQLで文字列と数値を変換する 数値をゼロ詰めフォーマットする方法
なお、lpad関数はMySQLだけではなく、Oracle、SQL Server、PostgreSQLといった主要DBMSで使える関数です。
数値を含めたデータ型の変換はcast関数を使用
なるべく特定のDBMSの拡張を使わずにデータ変換をおこなうなら、cast関数を使うのが良いでしょう。
cast関数は、Oracle、MySQL、SQL Server、PostgreSQLで用意されているデータ変換用の関数です。
ただし、DBMSによって一部拡張がある点に注意。
【関連記事】
▶SQL castはデータ型の明示的な変換 データベースによって、オプションに差異あり
まとめ
- Oracleでは、数値を文字列に変換するにはTO_CHARを使用
- 他のDBMSでは、CONVERT、DATE_FORMAT、LPADなどを併用する
- CAST関数は、主要DBMSで共通して使えるデータ変換関数
