CAST および CONVERT (Transact-SQL)
あるデータ型の式を SQL Server 2008 の別のデータ型の式に変換します。
.gif "トピック リンク アイコン")
構文
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
引数
expression
有効な式を指定します。data_type
対象のデータ型です。これには xml、bigint、sql_variant などがあります。別名データ型は使用できません。使用できるデータ型の詳細については、「データ型 (Transact-SQL)」を参照してください。length
対象のデータ型の長さを指定する整数です (省略可能)。既定値は 30 です。style
CONVERT 関数で expression を変換する方法を指定する整数式です。スタイルが NULL の場合は、NULL が返されます。範囲は、data_type で決まります。詳細については、「解説」を参照してください。
戻り値の型
data_type に変換された expression を返します。
説明
日付および時刻のスタイル
expression が日付や時刻のデータ型である場合、style には次の表に示すいずれかの値を指定できます。その他の値は 0 として処理されます。SQL Server では、アラビア式での日付形式がクウェート アルゴリズムによりサポートされます。
2 桁の年 (yy) (1) |
4 桁の年 (yyyy) |
標準 |
入力/出力 (3) |
|---|---|---|---|
- |
0 または 100 (1,2) |
既定値 |
mon dd yyyy hh:miAM (または PM) |
1 |
101 |
米国 |
mm/dd/yyyy |
2 |
102 |
ANSI |
yy.mm.dd |
3 |
103 |
イギリス/フランス |
dd/mm/yyyy |
4 |
104 |
ドイツ |
dd.mm.yy |
5 |
105 |
イタリア |
dd-mm-yy |
6 |
106(1) |
- |
dd mon yy |
7 |
107(1) |
- |
Mon dd, yy |
8 |
108 |
- |
hh:mi:ss |
- |
9 または 109 (1,2) |
既定値 + ミリ秒 |
mon dd yyyy hh:mi:ss:mmmAM (または PM) |
10 |
110 |
米国 |
mm-dd-yy |
11 |
111 |
日本 |
yy/mm/dd |
12 |
112 |
ISO |
yymmdd yyyymmdd |
- |
13 または 113 (1,2) |
ヨーロッパの既定値 + ミリ秒 |
dd mon yyyy hh:mi:ss:mmm(24h) |
14 |
114 |
- |
hh:mi:ss:mmm(24h) |
- |
20 または 120 (2) |
ODBC 標準 |
yyyy-mm-dd hh:mi:ss(24h) |
- |
21 または 121 (2) |
ODBC 標準 (ミリ秒を含む) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
- |
126 (4) |
ISO8601 |
yyyy-mm-ddThh:mi:ss.mmm (スペースなし) |
- |
127(6, 7) |
ISO 8601 (タイム ゾーン Z) |
yyyy-mm-ddThh:mi:ss.mmmZ (スペースなし) |
- |
130 (1,2) |
Hijri (5) |
dd mon yyyy hh:mi:ss:mmmAM |
- |
131 (2) |
Hijri (5) |
dd/mm/yy hh:mi:ss:mmmAM |
1 これらのスタイル値で返される結果は非決定的です。この場合は、すべての (yy) (2 桁の年) スタイルと、(yyyy) (4 桁の年) スタイルのサブセットが対象となります。
2 既定値 (style0 または 100、9 または 109、13 または 113、20 または 120、21 または 121) では、常に 4 桁の年 (yyyy) が返されます。
3datetime 型に変換する場合は入力になり、文字データに変換する場合は出力になります。
4 XML で使用するよう設計されています。datetime または smalldatetime 型から文字データに変換する場合の出力形式は、前の表に示したとおりです。
5 Hijri とはいくつかのバリエーションがあるカレンダー システムです。SQL Server ではクウェート アルゴリズムが使用されます。
.gif "重要な注意事項") 重要 |
|---|
SQL Server の既定では、2 桁の年は、終了年 2049 年を基準に解釈されます。つまり、2 桁の年である 49 年は 2049 年、50 年は 1950 年と解釈されます。クライアント アプリケーションの中には、オートメーション オブジェクトをベースとするクライアント アプリケーションのように、2030 年を終了年とするものも多くあります。SQL Server では構成オプション two digit year cutoff が用意されており、このオプションを使用して SQL Server の終了年の設定を変更でき、一貫した日付の処理を行うことができます。年は 4 桁で指定することを推奨します。 |
6 文字データを datetime または smalldatetime 型にキャストする場合のみサポートされます。日付部分または時刻部分のみを表す文字データは、datetime または smalldatetime データ型にキャストされます。時刻部分の指定がない場合、時刻は 00:00:00:000 に設定され、日付部分の指定がない場合、日付は 1900-01-01 に設定されます。
7. タイム ゾーン インジケーター Z は省略可能です。これを使用すると、タイム ゾーン情報が含まれる XML の datetime 値から、タイム ゾーン情報が含まれない SQL Serverdatetime 値へのマップが容易になります。Z は、タイム ゾーン UTC-0 を示すインジケーターです。他のタイム ゾーンは、+ または - の方向に HH:MM オフセットで示されます。たとえば、2006-12-12T23:45:12-08:00 のようになります。
smalldatetime 型を文字データに変換する場合、秒またはミリ秒を含むスタイルではそれらの位置に 0 が表示されます。datetime や smalldatetime 型の値を変換するときは、char や varchar 型の長さを適切に設定することにより、日付の不要な部分を切り捨てることができます。
時刻を含むスタイルを使用する文字データから datetimeoffset に変換すると、タイム ゾーン オフセットが結果に追加されます。
float 型スタイルと real 型スタイル
expression が float または real である場合、style には次の表に示すいずれかの値を指定できます。その他の値は 0 として処理されます。
値 |
出力 |
|---|---|
0 (既定値) |
最高 6 桁。該当する場合は、科学的表記法で使用します。 |
1 |
常に 8 桁。常に科学的表記法で使用します。 |
2 |
常に 16 桁。常に科学的表記法で使用します。 |
126, 128, 129 |
レガシ システムのために含まれていますが、将来のリリースでは廃止される可能性があります。 |
money 型スタイルと smallmoney 型スタイル
expression が money または smallmoney である場合、style には次の表に示すいずれかの値を指定できます。その他の値は 0 として処理されます。
値 |
出力 |
|---|---|
0 (既定値) |
小数点位置から左へ 3 桁ごとの位置にはコンマを挿入しません。また、小数点右側には 2 桁をとります。たとえば 4235.98 のようになります。 |
1 |
小数点位置から左へ 3 桁ごとにコンマを挿入し、小数点右側には 2 桁をとります。たとえば 3,510.92 のようになります。 |
2 |
小数点位置から左へ 3 桁ごとの位置にはコンマを挿入しません。また、小数点右側には 4 桁をとります。たとえば 4235.9819 のようになります。 |
126 |
スタイルを char(n) または varchar(n) に変換するときの style 2 に相当します。 |
xml スタイル
expression が xml である場合、style には次の表に示すいずれかの値を指定できます。その他の値は 0 として処理されます。
値 |
出力 |
|---|---|
0 (既定値) |
既定の解析動作を使用します。つまり、余分な空白を破棄し、内部の DTD サブセットを許可しません。 .gif "注意") 注意
xml データ型への変換では、SQL Server での余分な空白は XML 1.0 とは別の方法で処理されます。詳細については、「XML インスタンスの生成」を参照してください。
|
1 |
余分な空白を保持します。このスタイル設定では、既定の xml:space 処理が設定され、xml:space="preserve" が代わりに指定された場合と同じ動作が行われます。 |
2 |
限定的な内部 DTD サブセット処理を有効にします。 有効な場合、サーバーでは内部 DTD サブセットで提供される情報を次のように使用して、検証を行わない解析操作を実行できます。
パーサーでは外部 DTD サブセットが無視されます。また、パーサーでは XML 宣言を評価して standalone 属性が yes と no のどちらであるかを確認するのではなく、XML インスタンスをスタンドアロン ドキュメントと見立てて解析します。 |
3 |
余分な空白を保持し、限定的な内部 DTD サブセット処理を有効にします。 |
バイナリ スタイル
expression が binary(n)、varbinary(n)、char(n)、または varchar(n) である場合、style には次の表に示すいずれかの値を指定できます。表に記載されていないスタイル値の場合は、エラーが返されます。
値 |
出力 |
|---|---|
0 (既定値) |
ASCII 文字をバイナリ バイトに変換するか、バイナリ バイトを ASCII 文字に変換します。各文字またはバイトは 1:1 で変換されます。 data_type がバイナリ型である場合、文字 0x が結果の左側に追加されます。 |
1, 2 |
data_type がバイナリ型である場合、式は文字式であることが必要です。expression は偶数桁の 16 進数 (0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f) で構成される必要があります。style が 1 に設定されている場合は、式の先頭の 2 文字を文字 0x にする必要があります。式に奇数桁の文字が含まれているか、いずれかの文字が無効な場合は、エラーが発生します。 変換された式の長さが data_type の長さよりも長い場合、結果の右側が切り捨てられます。 変換された結果よりも長い固定長の data_types の場合、結果の右側に 0 が追加されます。 data_type が文字型である場合、式はバイナリ式であることが必要です。各バイナリ文字は、16 進値を表す 2 桁の英数文字に変換されます。変換された式の長さが data_type の長さよりも長い場合は、右側が切り捨てられます。 data_type が固定サイズの文字型で、変換された結果の長さが data_type の長さよりも短い場合、変換された式の右側に空白が追加され、偶数桁の 16 進数が維持されます。 style が 1 の場合、変換された結果の左側に文字 0x が追加されます。 |
暗黙的な変換
暗黙的な変換とは、CAST または CONVERT 関数を指定せずに実行される変換のことです。一方、明示的な変換には、CAST または CONVERT 関数を指定する必要があります。次の図は、SQL Server システムで提供されるデータ型に許可されている、すべての明示的および暗黙的なデータ型変換です。これには、xml、bigint、および sql_variant 型が含まれます。代入時に sql_variant 型からの暗黙的な変換は行われませんが、sql_variant 型への暗黙的な変換は行われます。
.gif "データ型変換テーブル")
datetimeoffset と、char、varchar、nchar、および nvarchar の各文字型との間で変換を行う場合、変換後のタイム ゾーン オフセットの部分は、-08:00 のように hh と mm の両方が常に 2 桁である必要があります。
| 注意 |
|---|
Unicode データでは常に偶数のバイト数が使用されるので、binary 型または varbinary 型と、Unicode がサポートするデータ型との間でデータを変換する場合は注意してください。たとえば、SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary) という変換を行うと、16 進数の 41 ではなく、4100 が返されます。 |
大きな値のデータ型
大きな値のデータ型では、それより小さな値のデータ型、つまり varchar、nvarchar および varbinary 型と同様の明示的および暗黙的な変換動作が行われます。ただし、次のガイドラインを考慮する必要があります。
image 型から varbinary(max) 型への変換およびその逆の変換は暗黙的な変換であり、text 型と varchar(max) 型の間、および ntext 型と nvarchar(max) 型の間の変換も暗黙的な変換です。
varchar(max) 型のような大きな値のデータ型から、varchar 型のような小さな値のデータ型への変換は暗黙的な変換ですが、大きな値が、小さなデータ型で指定された長さに対して大きすぎると、切り捨てが発生します。
varchar、nvarchar、または varbinary 型からそれぞれ対応する大きな値のデータ型への変換は、暗黙的に実行されます。
sql_variant 型から大きな値のデータ型への変換は明示的な変換です。
大きな値のデータ型は、sql_variant 型へは変換できません。
Microsoft.NET Framework 共通言語ランタイム (CLR) ユーザー定義型の変換方法については、「ユーザー定義型に対する操作」を参照してください。xml 型からの変換方法の詳細については、「XML インスタンスの生成」を参照してください。
xml データ型
明示的か暗黙的かにかかわらず、xml データ型を文字列またはバイナリのデータ型にキャストするとき、xml データ型の内容は一連のルールに基づいてシリアル化されます。このルールの詳細については、「XML データのシリアル化」を参照してください。XML から CLR ユーザー定義型へのキャスト方法の詳細については、「ユーザー定義型に対する操作」を参照してください。他のデータ型から xml データ型への変換方法については、「XML インスタンスの生成」を参照してください。
text および image データ型
データ型の自動変換は、text 型および image 型ではサポートされません。text データは文字データに、image データは binary または varbinary 型に明示的に変換できますが、最大の長さは 8,000 バイトに制限されます。文字を含む文字式を int 型に変換するなど、不適切な変換が試行された場合、SQL Server ではエラー メッセージが返されます。
出力の照合順序
CAST または CONVERT の入出力が文字列である場合、出力では入力と同じ照合順序および照合順序ラベルが使用されます。入力が文字列ではない場合、出力ではデータベースの既定の照合順序、および強制可能な既定照合順序の照合順序ラベルが使用されます。詳細については、「照合順序の優先順位 (Transact-SQL)」を参照してください。
出力に別の照合順序を割り当てるには、CAST 関数または CONVERT 関数の結果式に COLLATE 句を適用します。次に例を示します。
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
切り捨てと丸め処理の結果
char、nchar、nvarchar、varchar、binary、varbinary 型などの文字式やバイナリ式を異なるデータ型に変換する場合は、データを切り捨てて一部分だけが表示されることがあります。または、結果が短すぎて表示できないというエラーが返されます。char、varchar、nchar、nvarchar、binary、および varbinary 型への変換では、次の表に示す変換を除き、切り捨てが行われます。
変換前のデータ型 |
変換後のデータ型 |
結果 |
|---|---|---|
int、smallint、または tinyint |
char |
* |
|
varchar |
* |
|
nchar |
E |
|
nvarchar |
E |
money、smallmoney、numeric、decimal、float、または real |
char |
E |
|
varchar |
E |
|
nchar |
E |
|
nvarchar |
E |
* = 結果が短すぎて表示できない。E = 結果が短すぎて表示できない場合エラーが返される。
SQL Server では、元のデータから別のデータ型に変換し、再度元のデータ型に戻すラウンド トリップ変換の場合にだけ、異なるバージョンでも同じ結果が生成されることが保証されています。次にラウンド トリップ変換の例を示します。
DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
| 注意 |
|---|
binary 型の値を作成し、これを数値データ型に変換することは避けてください。SQL Server では、decimal または numeric 型の binary 型への変換結果が SQL Server のバージョン間で同じになることは保証されていません。 |
次の例では、短すぎて表示できない結果式を示します。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5
以下に結果セットを示します。
FirstName LastName Title Sick Leave
-------------- ------------- -------------------- -----------
Gustavo Achong Mr. *
Catherine Abel Ms. *
Kim Abercrombie Ms. *
Humberto Acevedo Sr. *
Pilar Ackerman Sra. *
(5 row(s) affected)
小数点の位置が異なるデータ型を変換すると、場合によって結果の値が切り捨てられるか丸められます。次の表はその動作です。
変換前 |
変換後 |
動作 |
|---|---|---|
numeric |
numeric |
丸め処理 |
numeric |
int |
切り捨て |
numeric |
money |
丸め処理 |
money |
int |
丸め処理 |
money |
numeric |
丸め処理 |
float |
int |
切り捨て |
float |
numeric |
丸め処理 |
float |
datetime |
丸め処理 |
datetime |
int |
丸め処理 |
たとえば、次の変換の結果は 10 です。
SELECT CAST(10.6496 AS int)
変換後のデータ型の小数点以下桁数が変換前のデータ型の小数点以下桁数より少ない場合は、値が丸められます。たとえば、次の変換の結果は $10.3497 です。
SELECT CAST(10.3496847 AS money)
数値ではない char、nchar、varchar、または nvarchar 型のデータが int、float、numeric、または decimal 型に変換されるとき、SQL Server ではエラー メッセージが返されます。SQL Server では、空文字列 (" ") が numeric または decimal 型に変換されるときにもエラーが返されます。
SQL Server 2005 以降のバージョンでは一部の datetime 変換が非決定的
SQL Server 2000 では、文字列から日付と時刻への変換は "決定的" とマークされます。ただし、これは次の表に示すスタイルに対しては当てはまりません。これらのスタイルの場合、その変換は言語の設定に依存します。SQL Server 2005 以降のバージョンでは、これらの変換が "非決定的" とマークされます。
次の表は、文字列から datetime への変換が非決定的であるスタイルを示します。
100 未満のすべてのスタイル1 |
106 |
107 |
109 |
113 |
130 |
1 スタイル 20 と 21 を除く
例
A. CAST と CONVERT の両方を使用する
次の各例では、表示価格の最初の桁が 3 である製品について製品名を取得し、その ListPrice を int 型に変換します。
-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO
-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
B. CAST を算術演算子と共に使用する
次の例では、今年のこれまでの売り上げ合計 (SalesYTD) を手数料のパーセンテージ (CommissionPCT) で割り、単一列の計算 (Computed) を行います。この結果は、最も近い整数に丸められた後、int 型に変換されます。
USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
以下に結果セットを示します。
Computed
------
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107
(14 row(s) affected)
C. CAST を使用して連結する
次の例では、CAST を使って、文字型でもバイナリ型でもない式を連結します。
USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO
以下に結果セットを示します。
ListPrice
------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09
(5 row(s) affected)
D. CAST を使用してテキストをさらに読みやすくする
次の例では、選択リストの中で CAST を使用し、Name 列を char(10) 型の列に変換します。
USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO
以下に結果セットを示します。
Name UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99
(3 row(s) affected)
E. CAST を LIKE 句と共に使用する
次の例では、money 列 SalesYTD を int 型に変換した後、LIKE 句と共に使用できるよう char(20) 列に変換します。
USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO
以下に結果セットを示します。
FirstName LastName SalesYTD SalesPersonID
---------------- ------------------- ---------------- -------------
Carol Elliott 2811012.7151 279
Julie Estes 219088.8836 288
Janeth Esteves 2241204.0424 289
(3 row(s) affected)
F. CONVERT または CAST を、型指定された XML と共に使用する
次に示す例では、CONVERT を使用して、xml data type により型指定された XML への変換を行います。
次の例では、空白、テキスト、およびマークアップのある文字列を、型指定された XML に変換し、すべての余分な空白 (ノード間の境界の空白) を削除します。
CONVERT(XML, '<root><child/></root>')
次の例では、空白、テキスト、およびマークアップのある同様の文字列を、型指定された XML に変換し、すべての余分な空白 (ノード間の境界の空白) を保持します。
CONVERT(XML, '<root> <child/> </root>', 1)
次の例では、空白、テキスト、マークアップのある文字列を、型指定された XML にキャストします。
CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)
その他の例については、「XML インスタンスの生成」を参照してください。
G. CAST および CONVERT を datetime データと共に使用する
次の例では、現在の日付と時刻を表示し、CAST を使用して現在の日付と時刻を文字列型に変更した後、CONVERT を使用して ISO 8901 形式で日付と時刻を表示します。
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO
以下に結果セットを示します。
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ------------------------------ ------------------------------
2006-04-18 09:58:04.570 Apr 18 2006 9:58AM 2006-04-18T09:58:04.570
(1 row(s) affected)
次の例は、前の例とほぼ逆になります。この例では、日付と時刻を文字型データとして表示し、CAST を使用して文字型データを datetime 型に変更します。さらに、CONVERT を使用して、文字型データを datetime 型に変換します。
SELECT
'2006-04-25T15:50:59.997' AS UnconvertedText,
CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,
CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO
以下に結果セットを示します。
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997
(1 row(s) affected)
H. CONVERT をバイナリ データおよび文字データと共に使用する
以下は、さまざまなスタイルを使用してバイナリ データと文字データを変換した結果を示す例です。
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'
以下に結果セットを示します。
Style 0, binary to character
----------------------------
Name
(1 row(s) affected)
--The following example shows how Style 1 can force the result
--to be truncated. The truncation is caused by
--including the characters 0x in the result.
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'
以下に結果セットを示します。
Style 1, binary to character
------------------------------
0x4E616D
(1 row(s) affected)
--The following example shows that Style 2 does not truncate the
--result because the characters 0x are not included in
--the result.
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'
以下に結果セットを示します。
Style 2, binary to character
------------------------------
4E616D65
(1 row(s) affected)
--Convert the character value 'Name' to a binary value.
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, character to binary'
以下に結果セットを示します。
Style 0, character to binary
----------------------------------
0x4E616D6500000000
(1 row(s) affected)
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'
以下に結果セットを示します。
Style 1, character to binary
----------------------------------
0x4E616D65
(1 row(s) affected)
SELECT CONVERT(binary(4), '4E616D65', 1) AS 'Style 2, character to binary'
以下に結果セットを示します。
Style 2, character to binary
----------------------------------
0x4E616D65
(1 row(s) affected)