SQL CAST/CONVERT関数を使った数値・文字列変換

By /

SQLにおけるデータ型変換のすべて：CASTとCONVERT徹底解説（約5000語）

はじめに：なぜデータ型変換が必要なのか

データベースを扱う上で、データ型は非常に重要です。数値、文字列、日付、真偽値など、データはそれぞれ特定の型を持っており、その型によって格納方法や操作方法が異なります。しかし、現実のデータ処理においては、異なる型のデータを組み合わせて使用したり、特定の形式で表示したり、計算を行ったりする必要が頻繁に発生します。

例えば：
* 数値データ（INT, DECIMALなど）を、レポートのために特定の桁数や通貨記号付きの文字列として表示したい。
* ユーザーが入力した文字列（例: "2023-10-27"や"123"）を、データベース内の日付型（DATE, DATETIMEなど）や数値型（INT, DECIMALなど）の列と比較したり、挿入したりしたい。
* 異なる精度を持つ数値型（例: DECIMAL(5,2)とDECIMAL(10,4)）間で計算を行いたい。
* 日付や時刻のデータを特定のフォーマット（例: YYYY/MM/DD HH:MI:SS）の文字列に変換したい。

このような場合に必要となるのが、「データ型変換（Type Casting または Type Conversion）」です。SQLには、この明示的なデータ型変換を行うための主要な関数として、CASTとCONVERTが用意されています。

多くのデータベースシステムは、ある程度の「暗黙的な型変換」を自動で行います。例えば、文字列の"123"と数値の456を加算しようとすると、SQLは自動的に文字列を数値に変換して計算を実行することがあります。しかし、この暗黙的な変換は予期しない結果やエラーを引き起こしたり、パフォーマンスを低下させたりする可能性があります。例えば、"abc"のような文字列を数値に変換しようとした場合、暗黙的な変換ではエラーになるか、あるいは期待しない結果（例: ゼロ）を返すかもしれません。また、WHERE句で列のデータ型と異なる型の値を比較する際に、列の値が変換されるとインデックスが利用できなくなり、処理速度が著しく低下することがあります（これをSARGabilityがない状態と呼びます）。

したがって、安全で予測可能、そして効率的なデータ処理のためには、データ型変換を「明示的に」行うことが強く推奨されます。この記事では、SQL標準で広くサポートされているCAST関数と、特にSQL Serverで強力な機能を持つCONVERT関数について、その詳細な使い方、豊富な例、注意点、そして主要なデータベースシステム間での違いを含めて、約5000語のボリュームで徹底的に解説します。

この旅を終える頃には、あなたはSQLにおけるデータ型変換の達人となり、自信を持って複雑なデータ操作を行えるようになっているでしょう。

データ型変換の基礎：SQLにおける主要なデータ型と変換の原則

CASTやCONVERT関数の詳細に入る前に、SQLにおけるデータ型の基本的な概念と、変換の原則について触れておきましょう。

SQLにおける主要なデータ型

SQLには様々なデータ型がありますが、変換の文脈で特によく登場するのは以下のカテゴリーです。

  1. 数値型 (Numeric Types):

    • 整数型: INT, INTEGER, SMALLINT, BIGINT, TINYINT など。小数点以下の情報を持たない。
    • 小数点数型: DECIMAL, NUMERIC, FLOAT, REAL, DOUBLE PRECISION など。精度やスケール（小数点以下の桁数）を持つもの（DECIMAL, NUMERIC）と、浮動小数点数として近似値を扱うもの（FLOAT, REAL）があります。
    • 通貨型: MONEY (SQL Serverなど一部DB)。

  2. 文字列型 (String Types):

    • 固定長文字列: CHAR, NCHAR。指定された長さの領域を常に確保します。
    • 可変長文字列: VARCHAR, NVARCHAR, TEXT など。格納するデータの長さに応じて領域を使います。Nプレフィックスを持つ型はUnicode文字を扱えます。

  3. 日付/時刻型 (Date/Time Types):

    • 日付: DATE (年月日)
    • 時刻: TIME (時分秒)
    • 日付と時刻: DATETIME, TIMESTAMP, SMALLDATETIME, DATETIME2 など。秒以下の精度やタイムゾーン情報を持つものもあります。

  4. バイナリ型 (Binary Types):

    • 固定長バイナリ: BINARY
    • 可変長バイナリ: VARBINARY, IMAGE, BLOB など。画像ファイルやその他の非構造化データを格納します。

  5. その他:

    • ブール型: BOOLEAN (多くのDBでサポートされるが、SQL ServerではBIT型で代用されることが多い)
    • XML型, JSON型 (一部DBでサポート)
    • 空間データ型 (Spatial Types) など

これらの型は、それぞれデータの表現形式、格納に必要な領域、そして利用可能な操作が異なります。異なる型の間でデータを交換したり操作したりする際には、しばしば型変換が必要になります。

暗黙的な型変換（Implicit Conversion）の危険性

前述したように、SQLは特定の状況下で自動的に型変換を行いますが、これに依存することは推奨されません。

例：
sql
-- 数値型列と文字列を比較
SELECT * FROM products WHERE price = '100';
このクエリでは、price列が数値型（例: DECIMAL）である場合、SQLは文字列'100'を数値100に変換してから比較を行う可能性があります。一見問題ないように見えますが、もしprice列にインデックスが貼られていても、この暗黙的な変換によってインデックスが使われず、フルスキャンが発生しパフォーマンスが低下する可能性があります。

また、以下のようなケースではエラーや予期しない結果を招くことがあります。
sql
-- 存在しない文字列を数値に変換しようとする
SELECT * FROM orders WHERE order_id = 'ABC'; -- order_idが数値型の場合
この場合、'ABC'は有効な数値ではないため、SQLは変換に失敗しエラーを発生させるか（DBによる）、あるいは変換に失敗したと見なして一致する行がないと判断する（滅多にないが可能性として）など、挙動が不安定になる可能性があります。

さらに、暗黙的な変換のルールはデータベースシステムによって微妙に異なる場合があります。あるDBでは成功する変換が、別のDBでは失敗したり、異なる結果になったりすることがあります。

このような理由から、データ型変換が必要な場合は、常にCASTまたはCONVERTを用いて「明示的に」変換を行うべきです。これにより、コードの意図が明確になり、予期しない挙動を防ぎ、多くの場合でパフォーマンスの問題も回避できます。

CAST関数の詳細：標準SQLのデータ型変換

CAST関数は、SQL標準で定義されているデータ型変換関数です。そのため、多くの主要なデータベースシステム（SQL Server, PostgreSQL, MySQL, Oracleなど）で共通して使用できます。異なるデータベースシステム間での互換性を保ちたい場合に特に有用です。

CAST関数の構文

CAST関数の基本的な構文は非常にシンプルです。

sql
CAST ( 式 AS データ型 )

  • 式 (expression): 変換したい値や、値を持つ列、あるいは計算結果など。
  • AS: 変換後のデータ型を指定するためのキーワード。
  • データ型 (data_type): 変換したい目標のデータ型。型の名前（例: INT, VARCHAR(50), DECIMAL(10,2), DATE）を指定します。

CAST関数の基本的な使い方と例

さまざまなデータ型間でCASTを使用する例を見てみましょう。

1. 数値型から文字列型への変換

数値を文字列として扱いたい場合に使います。例えば、数値のIDを他の文字列と連結する場合など。

“`sql
— INTからVARCHARへの変換
SELECT CAST(12345 AS VARCHAR(10));
— 結果: ‘12345’ (文字列)

— DECIMALからVARCHARへの変換
SELECT CAST(123.45 AS VARCHAR(10));
— 結果: ‘123.45’ (文字列)

— DECIMALの精度を超えた場合 (小数点以下の桁数)
SELECT CAST(123.4567 AS DECIMAL(5,2));
— 結果: 123.46 (DECIMAL, 丸められる)
— DECIMALをVARCHARに変換する場合、指定した精度・スケールに従うか、元の値に従うかはDBや状況による
SELECT CAST(CAST(123.4567 AS DECIMAL(5,2)) AS VARCHAR(10));
— 結果例 (DBによる): ‘123.46’

— 大きな数値をVARCHARに変換
SELECT CAST(123456789012345 AS VARCHAR(20));
— 結果: ‘123456789012345’ (文字列)
``VARCHARなどの文字列型に変換する場合、かっこ内に最大長を指定するのが一般的です (VARCHAR(10)`など)。指定した長さよりも変換後の文字列が長い場合、エラーになるか、または切り捨てられる可能性があります（これもDBに依存することがあります）。十分な長さを指定することが重要です。

2. 文字列型から数値型への変換

文字列で格納されている数値を、計算や数値比較のために数値型に変換します。

“`sql
— VARCHARからINTへの変換
SELECT CAST(‘123’ AS INT);
— 結果: 123 (数値)

— VARCHARからDECIMALへの変換
SELECT CAST(‘123.45’ AS DECIMAL(10,2));
— 結果: 123.45 (数値)

— DECIMAL型に変換する際に、元の文字列の精度・スケールと目標の型の精度・スケールが異なる場合
— 目標の型に合わせて丸めや切り捨てが行われる可能性があります。
SELECT CAST(‘123.456’ AS DECIMAL(5,2));
— 結果: 123.46 (DECIMAL, 丸められる)

— 文字列に不要な空白が含まれている場合 (多くのDBでは自動的にトリムされるが保証されない場合も)
SELECT CAST(‘ 123 ‘ AS INT);
— 結果例: 123 (数値)

— 変換できない文字列の場合
SELECT CAST(‘abc’ AS INT);
— 結果: エラーが発生します。多くのDBでは変換エラーとなります。
``
文字列を数値型に変換する場合、文字列が数値として有効な形式である必要があります。数字以外の文字（小数点、符号を除く）が含まれている場合、通常はエラーが発生します。SQL Serverなど一部のDBでは、先頭や末尾の空白は無視されることがありますが、他のDBではエラーになる可能性もあるため、事前にTRIM`関数などで空白を除去しておく方が安全です。

3. 数値型間の変換

異なる精度やスケールを持つ数値型、あるいは整数型と小数点数型の間で変換を行います。

“`sql
— INTからDECIMALへの変換
SELECT CAST(123 AS DECIMAL(10,2));
— 結果: 123.00 (DECIMAL)

— DECIMALからINTへの変換 (小数点以下は切り捨てられることが多い)
SELECT CAST(123.78 AS INT);
— 結果: 123 (INT) – 注意: 丸めではなく切り捨てが一般的ですが、DBによる違いもあり得ます。正確な挙動はDBのドキュメントを確認してください。

— DECIMALから別のDECIMALへの変換 (精度・スケールの変更)
SELECT CAST(123.4567 AS DECIMAL(5,2));
— 結果: 123.46 (DECIMAL, 丸められる)

— オーバーフローの可能性
— 例: 大きすぎるBIGINTをINTに変換しようとする
SELECT CAST(9876543210 AS INT); — INTの最大値を超える場合
— 結果: エラーが発生します。
``
数値型間の変換では、精度が失われたり（例:DECIMAL→INT`で小数点以下が切り捨て）、値が目標の型の範囲を超えた場合にエラー（オーバーフロー）が発生したりする可能性があります。変換後のデータ型は、変換元のデータの最大値や必要な精度を考慮して適切に選択する必要があります。

4. 文字列型から日付/時刻型への変換

日付や時刻を表す文字列を、DATEやDATETIMEなどの日付/時刻型に変換します。

“`sql
— 文字列からDATEへの変換 (標準的なYYYY-MM-DD形式)
SELECT CAST(‘2023-10-27’ AS DATE);
— 結果: 2023-10-27 (DATE)

— 文字列からDATETIMEへの変換 (標準的なYYYY-MM-DD HH:MI:SS形式)
SELECT CAST(‘2023-10-27 10:30:00’ AS DATETIME);
— 結果例 (DBによる): 2023-10-27 10:30:00.000 (DATETIME)

— DBが認識できる他の形式の文字列
SELECT CAST(’10/27/2023′ AS DATE); — MM/DD/YYYY形式
— 結果例 (DBや設定による): 2023-10-27 (DATE)
``
文字列から日付/時刻型への変換は、文字列の形式がデータベースシステムが認識できる標準的な形式または設定された形式と一致している必要があります。一致しない場合、変換エラーが発生します。様々な形式の文字列に対応するには、CONVERT関数のスタイル指定や、データベース固有の日付解析関数（PostgreSQLのTO_DATEなど）がより強力です。CAST`は比較的標準的な形式にのみ対応することが多いです。

5. 日付/時刻型から文字列型への変換

日付や時刻を特定のフォーマットの文字列として表示したい場合に使います。

sql
-- DATETIMEからVARCHARへの変換
SELECT CAST(GETDATE() AS VARCHAR(50)); -- GETDATE()は現在のDATETIMEを取得するSQL Server関数（DBにより異なる）
-- 結果例 (DBによる): 'Oct 27 2023 10:30AM' または '2023-10-27 10:30:00' など
日付/時刻型から文字列型への変換も可能ですが、CASTでは出力される文字列のフォーマットを細かく制御することは困難です。フォーマットを自由に指定したい場合は、CONVERT関数（スタイル指定）や、データベース固有の関数（PostgreSQLのTO_CHARなど）を使用するのが一般的です。

6. NULL値の扱い

CAST関数は、入力がNULLである場合、常にNULLを返します。

sql
SELECT CAST(NULL AS INT);
-- 結果: NULL
SELECT CAST(NULL AS VARCHAR(50));
-- 結果: NULL
これは予測可能な挙動であり、NULLの変換を特別にハンドリングする必要がない場合は便利です。

CAST関数を使用する際の注意点

  • 変換不可能な値: 変換元の値が目標のデータ型として有効な形式でない場合（例: 'abc'を数値に変換）、ほとんどのDBではエラーが発生します。
  • 精度の喪失とオーバーフロー: 数値型間の変換や、精度を持つ型（DECIMAL）から持たない型（INT）への変換では、情報の損失やオーバーフローの可能性があります。
  • 日付/時刻文字列の形式: CASTでの日付/時刻文字列の変換は、DBが認識できる形式に限定されることが多いです。柔軟な変換には他の関数を検討してください。
  • 文字列の長さ: 文字列型に変換する際は、十分な長さを指定しないとエラーやデータの切り捨てが発生する可能性があります。
  • DBによる挙動の違い: CASTは標準SQLですが、エラー処理の厳密さや、一部の変換（特に文字列↔日付/時刻）で認識できる形式など、細かい挙動はデータベースシステムによって異なる場合があります。常に使用しているDBのドキュメントを確認することが重要です。

CONVERT関数の詳細：主にSQL Serverにおける強力な変換ツール

CONVERT関数は、SQL Serverで主に使用されるデータ型変換関数です。CASTよりも多くの機能を持ち、特に日付/時刻型やバイナリデータの変換において、詳細な「スタイル」を指定できる点が大きな特徴です。他のデータベースシステム（MySQLなど）にもCONVERT関数は存在しますが、その構文や機能はSQL Serverのものとは異なるため、互換性が必要な場合はCASTを使用するか、各DB固有の関数を検討する方が良いでしょう。

本記事では、主にSQL ServerにおけるCONVERT関数について詳しく解説します。

CONVERT関数の構文 (SQL Server)

CONVERT関数の構文は以下のようになります。

sql
CONVERT ( データ型, 式 [, スタイル] )

  • データ型 (data_type): 変換したい目標のデータ型。CASTと同様に型の名前を指定します。
  • 式 (expression): 変換したい値や、値を持つ列、あるいは計算結果など。
  • スタイル (style) (オプション): 特に日付/時刻型から文字列型への変換や、バイナリデータとの変換において、出力または入力の形式を指定する整数値。すべてのデータ型変換でスタイルが使用できるわけではありません。

CASTとCONVERTの違い

主要な違いは以下の点です。

  1. 標準性: CASTは標準SQLであり、多くのDBで共通して使えます。CONVERTはSQL Serverをはじめとする一部のDB固有の関数です。
  2. 機能: CONVERTは、特に日付/時刻変換において「スタイル」を指定することで、出力形式を細かく制御できる強力な機能を持っています。CASTにはスタイルの概念はありません。
  3. 構文: CASTは CAST(式 AS データ型)、CONVERTは CONVERT(データ型, 式 [, スタイル]) と、引数の順序とスタイルの有無が異なります。

互換性を優先するならCAST、SQL Server環境で日付/時刻のフォーマットを詳細に制御したいならCONVERTと使い分けるのが一般的です。

CONVERT関数の基本的な使い方と例

CASTと同様の基本的な変換はCONVERTでも可能です。

“`sql
— INTからVARCHARへの変換
SELECT CONVERT(VARCHAR(10), 12345);
— 結果: ‘12345’ (文字列)

— VARCHARからINTへの変換
SELECT CONVERT(INT, ‘123’);
— 結果: 123 (数値)

— VARCHARからDECIMALへの変換
SELECT CONVERT(DECIMAL(10,2), ‘123.45’);
— 結果: 123.45 (数値)

— 文字列からDATEへの変換
SELECT CONVERT(DATE, ‘2023-10-27’);
— 結果: 2023-10-27 (DATE)
``
基本的な変換においては、CONVERTはCAST`とほぼ同じように使えます。ただし、引数の順序が逆であることに注意してください。

CONVERTの強力な機能：スタイルの詳細

CONVERT関数の最大の特徴は、日付/時刻型やバイナリデータとの変換においてスタイルを指定できることです。これにより、入力文字列の解析形式を指定したり、出力文字列の表示形式を細かく制御したりできます。

日付/時刻型と文字列型間の変換スタイル

日付/時刻型（DATE, DATETIME, SMALLDATETIME, DATETIME2など）と文字列型（CHAR, VARCHAR, NCHAR, NVARCHARなど）の間で変換を行う際に、スタイル番号を指定することで、特定のフォーマットに沿って変換を行うことができます。スタイルは整数値で指定し、多くの場合、世紀を含む形式と含まない形式で番号が分かれています（例: 101は世紀なし MM/DD/YY、121は世紀あり YYYY-MM-DD HH:MI:SS.mmm）。

よく使われる日付/時刻変換スタイルの一部を以下に示します。

スタイル番号 世紀 (yy) 世紀 (yyyy) 出力形式 例 (2023年10月27日 10時30分00秒)
日付
1 1 mm/dd/yy 10/27/23
101 121 mm/dd/yyyy 10/27/2023
2 2 yy.mm.dd 23.10.27
102 121 yyyy.mm.dd 2023.10.27
3 3 dd/mm/yy 27/10/23
103 121 dd/mm/yyyy 27/10/2023
4 4 dd.mm.yy 27.10.23
104 121 dd.mm.yyyy 27.10.2023
5 5 dd-mm-yy 27-10-23
105 121 dd-mm-yyyy 27-10-2023
6 6 dd Mon yy 27 Oct 23
106 121 dd Mon yyyy 27 Oct 2023
7 7 Mon dd, yy Oct 27, 23
107 121 Mon dd, yyyy Oct 27, 2023
8 8 hh:mi:ss (24h) 10:30:00
108 hh:mi:ss (24h) 10:30:00
日付と時刻
9 9 Mon dd yyyy hh:mi:ss:mmmAM/PM Oct 27 2023 10:30:00:000AM
109 121 Mon dd yyyy hh:mi:ss:mmmAM/PM Oct 27 2023 10:30:00:000AM
10 10 mm-dd-yy hh:mi:ss 10-27-23 10:30:00
110 121 mm-dd-yyyy hh:mi:ss 10-27-2023 10:30:00
11 11 yy/mm/dd hh:mi:ss 23/10/27 10:30:00
111 121 yyyy/mm/dd hh:mi:ss 2023/10/27 10:30:00
12 12 yymmdd hh:mi:ss 231027 10:30:00
112 121 yyyymmdd hh:mi:ss 20231027 10:30:00
ODBCエスケープ形式 (標準)
20 120 yyyy-mm-dd hh:mi:ss 2023-10-27 10:30:00
21 121 yyyy-mm-dd hh:mi:ss.mmm (+UTCオフセット) 2023-10-27 10:30:00.000
ISO 8601 (標準)
23 yyyy-mm-dd 2023-10-27
24 hh:mi:ss 10:30:00
25 121 yyyy-mm-dd hh:mi:ss.mmm 2023-10-27 10:30:00.000

※ このリストは一部であり、SQL Serverのバージョンによってサポートされるスタイルや挙動に違いがあります。正確な情報はSQL Serverのドキュメントを参照してください。
※ スタイル120と121は特にODBCやISO標準に準拠しており、日付と時刻の両方を含む場合に多く使われます。

スタイルを使った変換例 (SQL Server):

“`sql
— 現在日時 (GETDATE()) を様々な形式の文字列に変換
SELECT CONVERT(VARCHAR, GETDATE(), 101); — MM/DD/YYYY
— 結果例: ’10/27/2023′

SELECT CONVERT(VARCHAR, GETDATE(), 103); — DD/MM/YYYY
— 結果例: ’27/10/2023′

SELECT CONVERT(VARCHAR, GETDATE(), 112); — YYYYMMDD
— 結果例: ‘20231027’

SELECT CONVERT(VARCHAR, GETDATE(), 120); — YYYY-MM-DD HH:MI:SS
— 結果例: ‘2023-10-27 10:30:00’

SELECT CONVERT(VARCHAR, GETDATE(), 121); — YYYY-MM-DD HH:MI:SS.mmm
— 結果例: ‘2023-10-27 10:30:00.123’

SELECT CONVERT(VARCHAR, GETDATE(), 109); — Mon dd yyyy hh:mi:ss:mmmAM/PM
— 結果例: ‘Oct 27 2023 10:30:00:123AM’
“`

文字列から日付/時刻型への変換とスタイル:

スタイルは、文字列を日付/時刻型に変換する際にも利用できます。これにより、入力文字列が特定の非標準的なフォーマットである場合でも、そのフォーマットをSQL Serverに認識させることができます。

“`sql
— スタイル103 (DD/MM/YYYY) を指定して文字列をDATEに変換
SELECT CONVERT(DATE, ’27/10/2023′, 103);
— 結果: 2023-10-27 (DATE)

— スタイル101 (MM/DD/YYYY) を指定して文字列をDATEに変換
SELECT CONVERT(DATE, ’10/27/2023′, 101);
— 結果: 2023-10-27 (DATE)

— スタイルを指定しない場合、標準的な形式を期待する (または言語設定に依存)
SELECT CONVERT(DATE, ‘2023-10-27’); — スタイル省略
— 結果: 2023-10-27 (DATE)
“`
文字列から日付への変換でスタイルを指定する場合、入力文字列のフォーマットと指定したスタイルが一致しないとエラーが発生します。

バイナリ型と文字列型間の変換スタイル

CONVERT関数は、バイナリデータ（BINARY, VARBINARYなど）と文字列型の間で変換する際にもスタイルを使用できます。これはバイナリデータを16進数文字列として表現する場合などに便利です。

スタイル番号 出力形式 例 (バイナリ値 0x1A2B)
1 スタイル1 1A2B
2 スタイル2 1a2b

スタイルを使ったバイナリ変換例 (SQL Server):

“`sql
— VARBINARY値を16進数文字列に変換 (スタイル1)
SELECT CONVERT(VARCHAR(50), 0x1A2B3C4D, 1);
— 結果: ‘1A2B3C4D’ (文字列)

— VARBINARY値を16進数文字列に変換 (スタイル2 – 小文字)
SELECT CONVERT(VARCHAR(50), 0x1A2B3C4D, 2);
— 結果: ‘1a2b3c4d’ (文字列)

— 文字列からVARBINARY値に変換 (文字列は16進数形式である必要あり)
SELECT CONVERT(VARBINARY(50), ‘1A2B3C’, 1);
— 結果: 0x1A2B3C (VARBINARY)
“`

その他の変換スタイル

SQL ServerのCONVERT関数には、XML型や特定のデータ型で使用できる他のスタイルも存在します。しかし、これらは用途が限定的であり、すべてのユーザーが必要とするわけではありません。詳細についてはSQL Serverの公式ドキュメントを参照してください。

CONVERT関数を使用する際の注意点 (SQL Server)

  • ベンダー依存性: CONVERT関数はSQL Server固有（またはその構文を模倣した一部のDB）であり、他の主要なDBシステム（PostgreSQL, Oracleなど）では同じ構文や機能を持つ関数はありません。異なるDB間での移植性を重視する場合はCASTを使用するか、各DB固有の変換関数を検討してください。
  • スタイルの適用: スタイルはすべてのデータ型変換で利用できるわけではありません。主に日付/時刻型と文字列型、およびバイナリ型と文字列型の間で有効です。
  • 文字列の長さ: CONVERTで文字列型に変換する際も、CASTと同様に適切な長さを指定しないとエラーや切り捨てが発生する可能性があります。
  • 変換不可能な値とエラー: CASTと同様、変換元の値が目標のデータ型として有効な形式でない場合、エラーが発生します。日付/時刻変換では、指定したスタイルと入力文字列の形式が一致しない場合もエラーになります。
  • NULL値の扱い: CONVERT関数も、入力がNULLである場合、常にNULLを返します。これはCASTと同様の挙動です。

具体的な変換シナリオと豊富な例

ここでは、実際の開発や分析でよく遭遇する具体的なデータ型変換のシナリオと、それらをCASTやCONVERT（SQL Serverを中心に）を使ってどのように実現するかを、より実践的な例で見ていきます。

シナリオ1：数値データを特定のフォーマットの文字列として表示する

数値データをレポートやユーザーインターフェースで表示する際に、桁区切りや通貨記号、小数点以下の桁数などを制御したい場合があります。

例：価格列 (DECIMAL) を通貨フォーマットの文字列に変換 (SQL Server)

CONVERT関数には直接通貨フォーマットを指定するスタイルはありませんが、FORMAT関数（SQL Server 2012以降）や他の文字列操作関数と組み合わせることで実現できます。しかし、ここではCONVERT（またはCAST）で数値→文字列変換を行い、その後に加工するという考え方を示します。

“`sql
— 元の価格データ (DECIMAL)
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Price) VALUES
(1, ‘Laptop’, 1200.50),
(2, ‘Mouse’, 25.00),
(3, ‘Keyboard’, 75.99);

— 価格をそのまま文字列に変換 (桁区切りなし)
SELECT ProductName, CAST(Price AS VARCHAR(20)) AS PriceString FROM Products;
— 結果例:
— ProductName | PriceString
— ———– | ———–
— Laptop | 1200.50
— Mouse | 25.00
— Keyboard | 75.99

— SQL ServerのFORMAT関数を使った通貨フォーマット (より推奨される方法)
SELECT ProductName, FORMAT(Price, ‘C’, ‘en-US’) AS FormattedPrice FROM Products; — 米ドル形式
— 結果例:
— ProductName | FormattedPrice
— ———– | ————–
— Laptop | $1,200.50
— Mouse | $25.00
— Keyboard | $75.99

— SQL ServerでCONVERTを使った、限定的な文字列化と手動フォーマットの例（実用的ではないがCAST/CONVERTの限界を示すため）
— DECIMALを小数点以下2桁固定でVARCHARに変換し、手動で桁区切りや通貨記号を追加するのは複雑になる。
— CONVERT/CAST単体では高度な数値フォーマットは難しい。
SELECT ProductName, ‘$’ + CONVERT(VARCHAR(20), Price, 1) — スタイル1はあまり関係ないが形式上
FROM Products; — これでは $1200.50 となる。桁区切りがない。

— PostgreSQLの場合 (TO_CHAR関数が一般的)
— SELECT ProductName, TO_CHAR(Price, ‘FM999G999D00’) AS FormattedPrice FROM Products; — $1,200.50 のような形式 (TO_CHARは通貨記号も制御可能)

``
この例から分かるように、単純な数値→文字列変換はCAST/CONVERTで行えますが、表示用の高度なフォーマット（桁区切り、通貨記号など）は、FORMAT関数（SQL Server）、TO_CHAR関数（PostgreSQL, Oracle）、FORMAT関数（MySQL）など、データベースシステム固有の関数を使用するのが一般的で強力です。CAST/CONVERT`は、あくまで基本的な型変換の役割を担います。

シナリオ2：文字列データを数値データとして利用する

CSVファイルからインポートしたデータや、ユーザー入力が文字列としてデータベースに一時的に格納されている場合に、それらを数値として扱いたいことがあります。

例：文字列として保存された価格データを集計する

“`sql
— 文字列として価格が保存されているテーブル
CREATE TABLE TempProducts (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
PriceString VARCHAR(20) — 価格が文字列として保存されている
);

INSERT INTO TempProducts (ProductID, ProductName, PriceString) VALUES
(1, ‘Laptop’, ‘1200.50’),
(2, ‘Mouse’, ‘25.00’),
(3, ‘Keyboard’, ‘75.99’),
(4, ‘Monitor’, ‘999.99’),
(5, ‘Webcam’, ‘not_available’); — 不正なデータ

— 価格の合計を計算したい (PriceStringはVARCHAR)
— そのまま計算しようとするとエラーになるか、暗黙的な変換に依存してしまう
— SELECT SUM(PriceString) FROM TempProducts; — エラーまたは予期しない結果

— 明示的にDECIMALに変換してから合計を計算
SELECT SUM(CAST(PriceString AS DECIMAL(10, 2))) AS TotalPrice FROM TempProducts
WHERE PriceString NOT LIKE ‘%[^0-9.]%’; — ある程度数値形式かチェック

— 不正なデータ (‘not_available’) が含まれている場合、CASTはエラーになる。
— このクエリは不正なデータが含まれると全体が失敗する。
— CASTエラーを回避するには、事前にデータをクリーニングするか、エラー処理関数を使う。

— SQL ServerのTRY_CAST/TRY_CONVERTを使ったエラー回避 (変換できない場合はNULLを返す)
SELECT SUM(TRY_CAST(PriceString AS DECIMAL(10, 2))) AS TotalPrice FROM TempProducts;
— 結果例 (TRY_CAST/TRY_CONVERTが使えるDB):
— TotalPrice

— 2301.48 (不正なデータ ‘not_available’ は NULL に変換され、SUMから除外される)

— TRY_CAST/TRY_CONVERTがないDBの場合:
— CASE文とパターンマッチングでフィルタリング
SELECT SUM(CAST(PriceString AS DECIMAL(10, 2))) AS TotalPrice FROM TempProducts
WHERE PriceString NOT LIKE ‘%[^0-9.]%’ — 数字と小数点以外の文字が含まれていないことを確認 (簡易的)
AND PriceString NOT LIKE ‘%.%.’ — 小数点が複数ないことを確認
AND PriceString IS NOT NULL AND PriceString != ”; — 空文字列やNULLでないことを確認
— 注: このフィルタリングは完全ではない。通貨記号やカンマなどが含まれる場合はさらに複雑なパターンマッチングが必要。

— カンマが含まれる文字列の変換 (例: ‘1,200.50’)
— 直接CAST/CONVERTしようとするとエラーになる。事前にカンマを除去する必要がある。
— REPLACE関数などを使う
— SELECT CAST(REPLACE(‘1,200.50’, ‘,’, ”) AS DECIMAL(10,2)); — 結果: 1200.50
``
文字列から数値への変換では、入力文字列が有効な数値形式であることが非常に重要です。無効な文字列が含まれていると、変換時にエラーが発生しクエリ全体が失敗する可能性があります。TRY_CASTやTRY_CONVERT（SQL Server）のような関数は、変換に失敗した場合にエラーではなくNULL`を返すため、エラーを含むデータを安全に処理するのに役立ちます。他のDBでは、正規表現関数やCASE文を使った検証と組み合わせるなどの方法でエラーを回避する必要があります。

シナリオ3：日付/時刻データを様々なフォーマットの文字列として表示する

データベースに格納されている日付や時刻を、レポートやユーザーインターフェースで特定の形式（例: YYYY/MM/DD, MM-DD-YYYY HH:MI AM/PM）で表示したい場合に使います。

例：注文日 (DATETIME) を異なるフォーマットで表示 (SQL Server)

“`sql
— 注文テーブル
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATETIME,
Amount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, OrderDate, Amount) VALUES
(1, ‘2023-10-27 14:35:00’, 500.00),
(2, ‘2023-10-28 09:10:15’, 150.75),
(3, ‘2023-11-01 20:00:00’, 300.00);

— CONVERTを使って様々な日付フォーマットで表示
SELECT OrderID,
CONVERT(VARCHAR, OrderDate, 101) AS Date_MM_DD_YYYY, — MM/DD/YYYY
CONVERT(VARCHAR, OrderDate, 103) AS Date_DD_MM_YYYY, — DD/MM/YYYY
CONVERT(VARCHAR, OrderDate, 112) AS Date_YYYYMMDD, — YYYYMMDD
CONVERT(VARCHAR, OrderDate, 120) AS DateTime_YYYY_MM_DD_HH_MI_SS, — YYYY-MM-DD HH:MI:SS
CONVERT(VARCHAR, OrderDate, 109) AS DateTime_Mon_DD_YYYY_HH_MI_SS_MMM_AMPM — Mon dd yyyy hh:mi:ss:mmmAM/PM
FROM Orders;

— 結果例:
— OrderID | Date_MM_DD_YYYY | Date_DD_MM_YYYY | Date_YYYYMMDD | DateTime_YYYY_MM_DD_HH_MI_SS | DateTime_Mon_DD_YYYY_HH_MI_SS_MMM_AMPM
— ——- | ————— | ————— | ————- | —————————- | ————————————-
— 1 | 10/27/2023 | 27/10/2023 | 20231027 | 2023-10-27 14:35:00 | Oct 27 2023 2:35:0:0PM
— 2 | 10/28/2023 | 28/10/2023 | 20231028 | 2023-10-28 09:10:15 | Oct 28 2023 9:10:15:0AM
— 3 | 11/01/2023 | 01/11/2023 | 20231101 | 2023-11-01 20:00:00 | Nov 1 2023 8:0:0:0PM
``CONVERT関数とスタイル指定は、日付/時刻データを文字列にフォーマットする際に非常に強力です。これにより、アプリケーション側での日付フォーマット処理をデータベース側に任せることができます。ただし、スタイルはSQL Server固有であるため、他のDBではTO_CHAR`などの代替関数を使用する必要があります。

PostgreSQLの場合 (TO_CHAR関数):

sql
-- SELECT orderid,
-- TO_CHAR(orderdate, 'MM/DD/YYYY') AS Date_MM_DD_YYYY,
-- TO_CHAR(orderdate, 'DD/MM/YYYY') AS Date_DD_MM_YYYY,
-- TO_CHAR(orderdate, 'YYYYMMDD') AS Date_YYYYMMDD,
-- TO_CHAR(orderdate, 'YYYY-MM-DD HH24:MI:SS') AS DateTime_YYYY_MM_DD_HH_MI_SS,
-- TO_CHAR(orderdate, 'Mon DD YYYY HH:MI:SS AM') AS DateTime_Mon_DD_YYYY_HH_MI_SS_AMPM
-- FROM orders;
PostgreSQLのTO_CHAR関数は、より柔軟なフォーマット文字列を指定できますが、構文はCONVERTとは異なります。

シナリオ4：文字列データを日付/時刻データとして解析する

ログデータや外部システムからの入力など、日付や時刻が文字列として渡される場合に、それをデータベースの日付/時刻型として保存したり、日付計算や比較を行ったりするために変換が必要です。

例：文字列として受け取った日付をDATE型に変換し、期間でフィルタリングする

“`sql
— 外部からの入力文字列を想定
DECLARE @StartDateString VARCHAR(10) = ‘2023/10/27’;
DECLARE @EndDateString VARCHAR(10) = ‘2023/11/01’;

— 文字列をDATE型に変換して日付比較を行う
— SQL Serverの場合、スラッシュ区切り (スタイル111) でも認識可能
SELECT OrderID, OrderDate, Amount FROM Orders
WHERE OrderDate >= CONVERT(DATE, @StartDateString, 111)
AND OrderDate <= CONVERT(DATE, @EndDateString, 111);

— 結果例:
— OrderID | OrderDate | Amount
— ——- | ——————— | ——–
— 1 | 2023-10-27 14:35:00.000 | 500.00
— 2 | 2023-10-28 09:10:15.000 | 150.75
— 3 | 2023-11-01 20:00:00.000 | 300.00
``
文字列から日付/時刻への変換では、入力文字列の形式がデータベースが認識できる形式である必要があります。CONVERT`を使う場合は、入力文字列の形式に対応するスタイルを指定することで、変換の成功率を高めることができます。スタイルを省略した場合、データベースのデフォルト設定（言語設定など）に依存する標準的な形式が期待されます。

変換できない不正な文字列が含まれていると、数値変換と同様にエラーが発生する可能性があるため、TRY_CAST/TRY_CONVERTや他のエラー処理方法を検討してください。

シナリオ5：数値型間の変換（精度と範囲）

より広い範囲を扱える型から狭い型へ、あるいは精度が異なる型間で変換を行う場合があります。

例：DECIMALの精度を変更する、BIGINTをINTに変換する

“`sql
— DECIMALの精度を変更 (丸めが発生)
SELECT CAST(123.4567 AS DECIMAL(5, 2)); — 結果: 123.46
SELECT CONVERT(DECIMAL(5, 2), 123.4567); — 結果: 123.46

— DECIMALからINTへの変換 (切り捨て)
SELECT CAST(123.99 AS INT); — 結果: 123
SELECT CONVERT(INT, 123.99); — 結果: 123
— 注意: DBによっては四捨五入される場合もあります。テストが必要です。

— INTからBIGINTへの変換 (安全)
SELECT CAST(100000 AS BIGINT); — 結果: 100000
SELECT CONVERT(BIGINT, 100000); — 結果: 100000

— BIGINTからINTへの変換 (オーバーフローの可能性)
SELECT CAST(2147483647 AS INT); — INTの最大値までならOK
— 結果: 2147483647
SELECT CAST(2147483648 AS INT); — INTの最大値を超える
— 結果: エラー (Arithmetic overflow error converting expression to data type int.)
“`
数値型間の変換では、目標の型の範囲や精度に注意が必要です。範囲を超える値はオーバーフローエラーを引き起こし、精度を持つ型から持たない型への変換では小数部が切り捨てられたり丸められたりします。これらの挙動はデータベースシステムによって微妙に異なる場合があるため、重要な変換の場合はテストを行ってください。

変換エラーの処理と対策

データ型変換は非常に便利ですが、変換元の値が目標のデータ型として有効でない場合にエラーが発生するというリスクが常に伴います。特に外部からの入力データや、データ品質が保証されないテーブルを扱う際には、エラー処理が重要になります。

変換エラーの典型的なケース

  • 無効な形式: 'abc'を数値に変換、'2023-13-40'を日付に変換など。
  • 範囲外の値: 123456789012345（BIGINTの範囲）をINTに変換など。
  • 文字列長の不足: 長い文字列を短いVARCHAR型に変換など（DBによっては切り捨て）。
  • NULL値の扱い: NULLは変換してもNULLになりますが、意図しないNULLが生成される場合も考慮が必要です。

SQL ServerのTRY_CASTとTRY_CONVERT

SQL Server 2012以降では、変換エラーが発生した場合にエラーではなくNULLを返すTRY_CASTおよびTRY_CONVERT関数が導入されました。これは、データのクリーニングや検証を行う際に非常に有用です。

“`sql
— TRY_CASTを使った例
SELECT TRY_CAST(‘123’ AS INT); — 結果: 123 (成功)
SELECT TRY_CAST(‘abc’ AS INT); — 結果: NULL (失敗してもエラーにならない)
SELECT TRY_CAST(‘2023-10-27’ AS DATE); — 結果: 2023-10-27 (成功)
SELECT TRY_CAST(‘invalid-date’ AS DATE); — 結果: NULL (失敗してもエラーにならない)
SELECT TRY_CAST(‘2147483648’ AS INT); — 結果: NULL (オーバーフローで失敗)

— TRY_CONVERTを使った例 (スタイル指定も可能)
SELECT TRY_CONVERT(DATE, ’27/10/2023′, 103); — 結果: 2023-10-27 (成功)
SELECT TRY_CONVERT(DATE, ’10-27-2023′, 103); — 結果: NULL (スタイル不一致で失敗)
``TRY_CAST/TRY_CONVERTは、不正なデータが含まれている可能性のある列を処理する際に、クエリ全体がエラーで停止するのを防ぐのに役立ちます。変換結果がNULL`であるかどうかをチェックすることで、どの値が変換できなかったかを特定することも可能です。

sql
-- 不正なデータを含むPriceString列をTRY_CASTで処理し、NULLでないものだけ合計
SELECT SUM(T.ConvertedPrice)
FROM (
SELECT TRY_CAST(PriceString AS DECIMAL(10, 2)) AS ConvertedPrice
FROM TempProducts
) AS T
WHERE T.ConvertedPrice IS NOT NULL;

その他のDBにおけるエラー処理の代替手段

TRY_CAST/TRY_CONVERTのような組み込み関数がないデータベースシステムでは、以下のような方法でエラーを回避または処理する必要があります。

  • CASE文と文字列関数/正規表現: 変換前にLIKE演算子、SUBSTRING、PATINDEX、正規表現関数（DBがサポートしていれば）などを使って文字列の内容をチェックし、変換可能であると判断できた場合にのみCASTなどの変換関数を適用する。変換できない場合はNULLやデフォルト値を返すようにCASE文で分岐させます。
  • 外部ツール/スクリプト: データのインポートや処理の前に、アプリケーションコードやスクリプトでデータのバリデーションとクリーニングを行う。
  • エラー発生時の挙動設定: 一部のDBでは、変換エラーが発生した場合に処理を継続するか中断するかを設定できる場合があります（ただし、これは通常、データベース全体の挙動に影響するため慎重な検討が必要）。

ベストプラクティス

  • 常に明示的な型変換を使用する: 暗黙的な変換に頼るのではなく、CASTやCONVERTを使って変換の意図を明確にする。
  • 変換可能なデータのみを変換する: 変換元のデータが目標の型として有効であることを、可能な限り事前に保証する（入力時のバリデーション、データのクリーニング）。
  • エラー処理を組み込む: 不正なデータが含まれる可能性のあるシナリオでは、TRY_CAST/TRY_CONVERTやCASE文などを使ってエラーを適切に処理する。
  • 変換後のデータ型を適切に選択する: 変換後のデータ型は、変換元のデータの範囲、精度、そしてその後の計算や表示に必要な特性を考慮して選択する。
  • ベンダー固有関数と標準関数の使い分け: 互換性を重視する場合はCASTを、特定のDB（特にSQL Server）の高度な機能（スタイル）が必要な場合はCONVERTを検討する。

パフォーマンスへの影響：変換とインデックス

データ型変換は、クエリのパフォーマンスに大きな影響を与える可能性があります。特にWHERE句やJOIN句など、大量のデータをフィルタリングしたり結合したりする条件式の中で変換を行う場合は注意が必要です。

SARGabilityの喪失

SARGable（Search Argument-able）とは、クエリの条件式がインデックスを使って効率的に検索できる形式であるかどうかを示す言葉です。条件式に列に対して行われる関数呼び出しや型変換が含まれている場合、その列に対するインデックスが利用できなくなる（SARGabilityが失われる）ことがあります。

例：文字列として保存された日付列を変換して日付と比較する

“`sql
— OrderDateString列がVARCHARで、インデックスが貼られているとする
CREATE TABLE OrdersStringDate (
OrderID INT PRIMARY KEY,
OrderDateString VARCHAR(20), — 日付が文字列で保存
Amount DECIMAL(10, 2)
);
CREATE INDEX IX_OrderDateString ON OrdersStringDate (OrderDateString); — OrderDateStringにインデックス

INSERT INTO OrdersStringDate (OrderID, OrderDateString, Amount) VALUES
(1, ‘2023-10-27’, 500.00),
(2, ‘2023-10-28’, 150.75),
(3, ‘2023-11-01’, 300.00);

— 文字列列を日付に変換して日付リテラルと比較
— このクエリは、多くのDBでOrderDateString列のインデックスを利用できない可能性が高い
SELECT * FROM OrdersStringDate
WHERE CAST(OrderDateString AS DATE) >= ‘2023-10-28’; — 列に対してCASTを適用

— より効率的な方法：日付リテラルを文字列に変換して文字列列と比較
— OrderDateString列のインデックスが利用できる可能性が高い
SELECT * FROM OrdersStringDate
WHERE OrderDateString >= CAST(‘2023-10-28’ AS VARCHAR(20)); — リテラルに対してCASTを適用
— または WHERE OrderDateString >= ‘2023-10-28’; (暗黙的な変換がうまく機能する場合もあるが、明示が安全)
“`

最初のクエリでは、データベースシステムはOrderDateString列の各値をDATE型に変換してから比較を行います。この変換処理はインデックスの構造（ソート済みの文字列値）を「破壊」してしまうため、データベースはインデックスを使えず、テーブル全体をスキャンする必要が生じます。

二番目のクエリでは、比較対象のリテラル（'2023-10-28'）をOrderDateString列と同じVARCHAR型に変換しています。これにより、比較は文字列型同士で行われるため、OrderDateString列に貼られたインデックスを効率的に利用して該当する行を素早く見つけることができます。

原則: WHERE句やJOIN句の条件式では、可能であれば列に対してではなく、リテラルや変数に対して変換を行うようにします。これにより、列に貼られたインデックスが利用可能になり、クエリのパフォーマンスが向上します。

解決策：データ型の正規化

パフォーマンス問題を根本的に解決する最善の方法は、データベース設計において適切なデータ型を選択し、異なる型のデータを比較するために頻繁な変換を必要としないようにすることです。

上記の例で言えば、もし日付として利用するデータであれば、最初からDATE型またはDATETIME型の列として格納すべきです。そうすれば、日付リテラルとの比較は型変換なしで行え、日付列に貼られたインデックスを常に効率的に利用できます。

“`sql
— OrderDate列がDATETIMEで、インデックスが貼られているとする
CREATE TABLE OrdersProperDate (
OrderID INT PRIMARY KEY,
OrderDate DATETIME, — 日付が適切な型で保存
Amount DECIMAL(10, 2)
);
CREATE INDEX IX_OrderDate ON OrdersProperDate (OrderDate); — OrderDateにインデックス

INSERT INTO OrdersProperDate (OrderID, OrderDate, Amount) VALUES
(1, ‘2023-10-27 10:00:00’, 500.00),
(2, ‘2023-10-28 11:00:00’, 150.75),
(3, ‘2023-11-01 12:00:00’, 300.00);

— 日付列と日付リテラルを直接比較 (型変換なし)
— OrderDate列のインデックスが効率的に利用される
SELECT * FROM OrdersProperDate
WHERE OrderDate >= ‘2023-10-28’; — 文字列リテラルはDBが日付型に暗黙変換してくれることが多い（安全なケース）
— または明示的に: WHERE OrderDate >= CAST(‘2023-10-28’ AS DATETIME);
“`
このように、適切なデータ型でデータを格納することが、パフォーマンス面でも保守性・可読性でも最良のアプローチです。型変換は、データの表示形式を変更したり、一時的な処理のために異なる型のデータを組み合わせたりする場合に限定的に使用するのが理想的です。

主要データベースシステムでの違い

前述したように、CASTは標準SQLですが、CONVERTはSQL Serverなど一部のDBに固有です。他の主要なデータベースシステムでは、型変換のために異なる関数や構文を使用します。

PostgreSQL

  • CAST関数: 標準構文 CAST(式 AS データ型) をサポートしています。
  • ::演算子: PostgreSQL独自の、より簡潔な型変換構文です。式::データ型 の形式で使用します。CASTと同じ機能を提供します。
    sql
    SELECT 123::VARCHAR; -- 結果: '123'
    SELECT '2023-10-27'::DATE; -- 結果: 2023-10-27
  • TO_CHAR, TO_DATE, TO_NUMBER関数: Oracle互換の強力な型変換関数です。特に日付、数値、文字列間の変換で、柔軟なフォーマット指定が可能です。これらはSQL ServerのCONVERTのスタイル指定に似た機能を提供します。
    sql
    SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS'); -- 日付→文字列 (指定フォーマット)
    SELECT TO_DATE('27-10-2023', 'DD-MM-YYYY'); -- 文字列→日付 (指定フォーマットで解析)
    SELECT TO_NUMBER('1,200.50', 'FM9G999D00'); -- 文字列→数値 (指定フォーマットで解析)

MySQL

  • CAST関数: 標準構文 CAST(式 AS データ型) をサポートしています。
  • CONVERT関数: MySQLにもCONVERT関数がありますが、構文が異なります。CONVERT(式, データ型) または CONVERT(式 USING 文字セット) の形式で使用し、SQL Serverのようなスタイル指定機能はありません。CASTと同様の基本的な型変換機能を提供します。文字セット間の変換にも使用されます。
    sql
    SELECT CAST('123' AS SIGNED INTEGER); -- 結果: 123
    SELECT CONVERT('123', SIGNED INTEGER); -- 結果: 123
    MySQLのデータ型名はSQL Serverと微妙に異なる場合があります（例: SIGNED INTEGER, DECIMAL(P, S)など）。
  • FORMAT関数: 数値を桁区切り付きの文字列にフォーマットする関数です。小数点以下の桁数も指定できます。
    sql
    SELECT FORMAT(12345.6789, 2); -- 結果: '12,345.68'
  • 日付/時刻関数: DATE_FORMAT, STR_TO_DATEなどの関数が、文字列と日付/時刻型間の柔軟な変換に使われます。
    sql
    SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s'); -- 日付→文字列
    SELECT STR_TO_DATE('27/10/2023', '%d/%m/%Y'); -- 文字列→日付

Oracle

  • CAST関数: 標準構文 CAST(式 AS データ型) をサポートしています。オブジェクト型やコレクション型など、より複雑な型にも使用できます。
  • TO_CHAR, TO_DATE, TO_NUMBER関数: PostgreSQLと同様に、Oracleのこれら関数は非常に強力で、日付、数値、文字列間の変換とフォーマットにおいて標準的に使用されます。SQL ServerのCONVERTのスタイル指定よりもさらに柔軟なフォーマット文字列を指定できます。
    sql
    SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'); -- 日付→文字列
    SELECT TO_DATE('27-OCT-2023', 'DD-MON-YYYY'); -- 文字列→日付
    SELECT TO_NUMBER('1,200.50', '9G999D00'); -- 文字列→数値

まとめ：適切な変換でSQLをマスターする

SQLにおけるデータ型変換は、異なる種類のデータを組み合わせて処理したり、特定の形式で表示したりするために不可欠な技術です。この記事では、標準SQLのCAST関数と、特にSQL Serverで強力なCONVERT関数を中心に、その使い方、機能、そして注意点について詳しく解説しました。

  • CAST関数:

    • 標準SQLであり、多くのデータベースシステムで共通して使用できます。
    • 構文は CAST(式 AS データ型) とシンプルです。
    • 互換性を重視する場合に適しています。
    • 日付/時刻変換のフォーマット制御には限界があります。

  • CONVERT関数:

    • 主にSQL Serverで使用される関数です（他のDBにも同名の関数がありますが、機能は異なります）。
    • 構文は CONVERT(データ型, 式 [, スタイル]) です。
    • 特に日付/時刻型と文字列型の間の変換において、豊富な「スタイル」を指定することで、入力の解析や出力のフォーマットを細かく制御できます。
    • SQL Server環境で高度な日付フォーマットが必要な場合に強力です。

  • データ型変換の重要性:

    • 異なるデータ型を安全かつ正確に操作するために必要です。
    • 暗黙的な変換は予期しないエラーやパフォーマンス問題を引き起こす可能性があるため、避けるべきです。常に明示的な変換（CASTまたはCONVERTなど）を使用することが推奨されます。

  • エラー処理:

    • 変換元の値が無効な形式である場合、変換エラーが発生します。
    • SQL ServerではTRY_CASTやTRY_CONVERTを使うことで、エラーではなくNULLを返すようにでき、不正なデータを含む場合でもクエリ全体が失敗するのを防げます。
    • 他のDBでは、CASE文や文字列/正規表現関数を使った事前チェックなどで対応が必要です。

  • パフォーマンスへの影響:

    • WHERE句やJOIN句で列に対して変換を行うと、インデックスが利用できなくなり（SARGabilityが失われ）、パフォーマンスが著しく低下する可能性があります。
    • 可能な限り、リテラルや変数に対して変換を行うようにするか、根本的に適切なデータ型でデータを格納することが最善の対策です。

  • データベースシステムによる違い:

    • PostgreSQLでは::演算子やTO_CHAR/TO_DATE/TO_NUMBER関数、MySQLではFORMATやDATE_FORMAT/STR_TO_DATE関数、OracleではTO_CHAR/TO_DATE/TO_NUMBER関数など、各DBに固有の強力な変換・フォーマット関数が存在します。使用するDBのドキュメントを参照し、適切な関数を選択することが重要です。

データ型変換はSQLの強力なツールですが、その挙動や限界を理解し、慎重に使用する必要があります。この記事で解説した内容を参考に、あなたのSQLクエリでデータ型変換を正確かつ効率的に活用してください。正確なデータ操作は、信頼性の高いシステム構築の基盤となります。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール