SQLでデータ型の変換
データベースを利用していると、ある型のデータを別の型として扱いたくこともあるでしょう。
SQLでデータ型の変換を行いたい場合は「CAST関数」または「CONVERT関数」を使います。
それぞれの関数の使い方をサンプル付きで解説します。
CAST関数
CAST関数は、引数に指定した値のデータ型を別のデータ型に変換できる関数です。
CAST関数を使うには、次のように記述します。
CAST(変換する値 AS 変換する型);
CAST関数で変換できる型には決まりがあり、次の型に変換可能です。
- BINARY[(N)]
- CHAR[(N)]
- DATE
- DATETIME
- DECIMAL[(M[,D])]
- SIGNED [INTEGER]
- TIME
- UNSIGNED [INTEGER]
ではサンプルを見て確認して見ましょう。
確認用に、次のようなテーブルを作成しました。
mysql> CREATE TABLE cast_tb(番号 int, 商品名 varchar(20), 値段 int); Query OK, 0 rows affected (0.23 sec)
このテーブルに次のようにデータを追加します。
mysql> insert into cast_tb values(101, '商品1', 1200); mysql> insert into cast_tb values(102, '商品2', 1600); mysql> insert into cast_tb values(103, '商品3', 2100);
では「値段」カラムのデータ型をCHAR型に変換してみましょう。
次のように記述します。
mysql> SELECT CAST(値段 AS CHAR(10)) FROM cast_tb; +--------------------------+ | CAST(値段 as CHAR(10)) | +--------------------------+ | 1200 | | 1600 | | 2100 | +--------------------------+ 3 rows in set (0.02 sec)
見た目にはわかりづらいですが、INT型からCHAR型に変換できました。
例えば、INT型である値段の末尾に「円」という文字列を連結したい場合を考えてみましょう。
そのまま連結を行うのは、数値と文字列の異なる型を連結することになり、定義されていない演算を行うことになります。
DBMSによってはうまく動作しない可能性もあるでしょう。
そのため、値段カラムの型を文字列に揃えて連結する方が、安全かつ確実な方法と言えます。
mysql> SELECT 番号, 商品名, CONCAT(CAST(値段 AS CHAR(10)), '円') AS 値段 FROM cast_tb; +--------+-----------+---------+ | 番号 | 商品名 | 値段 | +--------+-----------+---------+ | 101 | 商品1 | 1200円 | | 102 | 商品2 | 1600円 | | 103 | 商品3 | 2100円 | +--------+-----------+---------+ 3 rows in set (0.00 sec)
値段カラムを文字列に変換して「円」という文字を追加し、データを取得しました。
文字列の連結には「CONCAT関数」を使用しています。
CONCAT関数については、次の記事で詳しく解説しているので参考までに。
【関連記事】
▶︎SQL concat 複数文字列を連結する NULLが入っていた場合は無視
ちなみに、MySQLであれば数値と文字列を連結することは可能です。
mysql> SELECT 番号, 商品名, CONCAT(値段, '円') AS 値段 FROM cast_tb; +--------+-----------+---------+ | 番号 | 商品名 | 値段 | +--------+-----------+---------+ | 101 | 商品1 | 1200円 | | 102 | 商品2 | 1600円 | | 103 | 商品3 | 2100円 | +--------+-----------+---------+ 3 rows in set (0.01 sec)
CONVERT 関数
CONVERT関数もCAST関数と同様に、引数に指定した値のデータ型を別のデータ型に変換できる関数です。
CONVERT関数を使うには、次のように記述します。
CONVERT(変換する値, 変換する型);
CONVERT関数も変換できる型には決まりがあり、CAST関数と同じになります。
では、CONVERT関数で値段カラムをCHAR型に変換してみます。
mysql> SELECT CONVERT(値段, CHAR(10)) FROM cast_tb; +---------------------------+ | CONVERT(値段, CHAR(10)) | +---------------------------+ | 1200 | | 1600 | | 2100 | +---------------------------+ 3 rows in set (0.02 sec)
このようにCHAR型に変換できました。
ここまで見ると、CAST関数とCONVERT関数の違いがわからないかもしれません。
両者とも基本的な機能は同じですが、CONVERT関数は元々SQL Serverに固有の関数で、日付や時刻、小数、通貨記号を変換する場合の柔軟性があります。
一方CAST関数が、ANSI SQLに準拠するために後から追加された関数です。
CONVERT関数と比べると、機能面で劣っています。
さまざまなデータ型に変換
ここでは、さまざまな型に変換する例を見ていきましょう。
文字列型→数値型にデータ型変換
先ほどは数値型（INT）を文字列型（CHAR）に変換したので、こちらでは逆で実行してみましょう。
cast_tbの値段カラムを文字列型に変更します。
mysql> show columns from cast_tb; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | 番号 | int | YES | | NULL | | | 商品名 | varchar(20) | YES | | NULL | | | 値段 | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
数値型に変換する場合は「SIGNED」か「UNSIGNED」を使いましょう。
SIGNEDは符号付きのデータ型で、UNSIGNEDは符号なしのデータ型です。
mysql> SELECT CAST(値段 AS SIGNED) FROM cast_tb; +------------------------+ | CAST(値段 as SIGNED) | +------------------------+ | 1200 | | 1600 | | 2100 | +------------------------+ 3 rows in set (0.00 sec) mysql> SELECT CONVERT(値段, SIGNED) FROM cast_tb; +-------------------------+ | CONVERT(値段, SIGNED) | +-------------------------+ | 1200 | | 1600 | | 2100 | +-------------------------+ 3 rows in set (0.00 sec)
文字列型→日付型にデータ型変換
文字列型から日付型に変換してみます。
mysql> SELECT CAST('20200731' AS DATE);
+--------------------------+
| CAST('20200731' AS DATE) |
+--------------------------+
| 2020-07-31 |
+--------------------------+
1 row in set (0.01 sec)
mysql> SELECT CONVERT('20200731161927', DATETIME);
+-------------------------------------+
| CONVERT('20200731161927', DATETIME) |
+-------------------------------------+
| 2020-07-31 16:19:27 |
+-------------------------------------+
1 row in set (0.00 sec)
日付型→文字列型にデータ型変換
では逆に日付型を文字列型に変換します。
ここでは現在日時を取得できる「DATE関数」と「NOW関数」を使いました。
それぞれの関数については以下の記事をチェックしてみてください。
【関連記事】
▶︎【WHERE DATE句】MySQL 日付・時間処理関数の使い方まとめ10選
mysql> SELECT CONVERT(DATE(NOW()), SIGNED); +------------------------------+ | CONVERT(DATE(NOW()), SIGNED) | +------------------------------+ | 20200731 | +------------------------------+ 1 row in set (0.01 sec) mysql> SELECT CAST(NOW() AS SIGNED); +-----------------------+ | CAST(NOW() AS SIGNED) | +-----------------------+ | 20200731162224 | +-----------------------+ 1 row in set (0.01 sec)
それぞれ文字列型として変換ができました。
文字列の大文字・小文字を変換
SQL文字列を大文字や小文字に変換したい場合は「UPPER関数」「LOWER関数」を使います。
ただし、MySQLとAccessは「UCASE関数」「LCASE関数」も使えます。
それぞれ書式は次の通りです。
■小文字→大文字に変換 UPPER(変換したい文字列); UCASE(変換したい文字列); ■大文字→小文字に変換 LOWER(変換したい文字列); LCASE(変換したい文字列);
実際に文字列を変換してみましょう。
mysql> select UPPER('potepan');
+------------------+
| UPPER('potepan') |
+------------------+
| POTEPAN |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT UCASE('potepan');
+------------------+
| UCASE('potepan') |
+------------------+
| POTEPAN |
+------------------+
1 row in set (0.02 sec)
mysql> SELECT LOWER('POTEPAN');
+------------------+
| LOWER('POTEPAN') |
+------------------+
| potepan |
+------------------+
1 row in set (0.01 sec)
mysql> SELECT LCASE('POTEPAN');
+------------------+
| LCASE('POTEPAN') |
+------------------+
| potepan |
+------------------+
1 row in set (0.00 sec)
それぞれ期待した通りの表示になっています。
文字列の先頭文字だけ大文字に変換
文字列の先頭文字だけを大文字に変換したい場合は「INITCAP関数」を使いましょう。
INITCAP関数は、OracleやSQL Server、PostgreSQLで使用可能です。
サンプルで表示を確認してみます。
mydb=# SELECT INITCAP('potapan');
initcap
---------
Potapan
(1 row)
このように、「potepan」の先頭の「p」だけが大文字になっているのがわかります。
SQLスキーマ変換ツール
SQLクエリを他のDBMSに合わせて変換したい場合は、「SQLines」というオンライン上のツールを使用すると便利です。
変換前のデータベーススキーマを、変換先のデータベースに合わせてスキーマ変換してくれます。
