CAST および CONVERT (Transact-SQL)

  • [アーティクル]

更新 : 2006 年 7 月 17 日

あるデータ型の式を別のデータ型の式に明示的に変換します。CAST と CONVERT では同じような機能が提供されます。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

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
    nchar、nvarchar、char、varchar、binary、または varbinary 型のパラメータで、省略可能です。CONVERT の場合、length を指定しない限り、既定値の 30 文字になります。

  • style
    datetime または smalldatetime のデータを文字データ (nchar、nvarchar、char、varchar、nchar、または nvarchar データ型) に変換したり、既知の日付または時刻形式の文字データを datetime または smalldatetime データに変換するときに使用する日付形式を指定します。あるいは、float、real、money、または smallmoney データを文字データ (nchar、nvarchar、char、varchar、nchar、または nvarchar データ型) に変換するときに使用する文字形式を指定します。style が NULL の場合、返される結果も NULL になります。

    ms187928.note(ja-jp,SQL.90).gifメモ :
    SQL Server では、ここに示した形式を、記載されている CONVERT 実行後のデータ型と組み合わせて使用できます。記載されていない形式、または組み合わせでは使用できません。サポートされていない形式は使用しないでください。サポートされていない形式を使用したり、形式と変換後のデータ型をサポートされていない組み合わせで使用したりすると、エラーや信頼できない結果が返される場合があります。SQL Server のどのリリースでもそのような結果になるとは限りません。

    SQL Server では、アラビア式での日付形式がクウェート アルゴリズムによりサポートされます。

    次の表の左側の 2 つの列は、datetime または smalldatetime 型から文字データに変換するときに使用できる style の値です。年を 4 桁で表現する (yyyy) 場合は、style の値に 100 を足します。

    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/yy

    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

    -

    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 2005 ではクウェート アルゴリズムが使用されます。

    ms187928.note(ja-jp,SQL.90).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 Server datetime 値へのマップが容易になります。Z は、タイム ゾーン UTC-0 を示すインジケータです。他のタイム ゾーンは、+ または - の方向に HH:MM オフセットで示されます。たとえば、2006-12-12T23:45:12-08:00 のようになります。

    smalldatetime 型を文字データに変換する場合、秒またはミリ秒を含むスタイルではそれらの位置に 0 が表示されます。datetime や smalldatetime 型の値を変換するときは、char や varchar 型の長さを適切に設定することにより、日付の不要な部分を切り捨てることができます。

    次の表は、float または real 型を文字データに変換するときに使用できる style の値です。

    値 出力

    0 (既定値)

    最高 6 桁。該当する場合は、科学的表記法で使用します。

    1

    常に 8 桁。常に科学的表記法で使用します。

    2

    常に 16 桁。常に科学的表記法で使用します。
    ms187928.note(ja-jp,SQL.90).gifメモ :
    float および real からの変換で、style 126 が指定されている場合、それぞれの出力は style 値 2 および 1 と同じになります。

    次の表は、money または smallmoney 型を文字データに変換するときに使用できる style の値です。

    値 出力

    0 (既定値)

    小数点位置から左へ 3 桁ごとの位置にはコンマを挿入しません。また、小数点右側には 2 桁をとります。たとえば 4235.98 のようになります。

    1

    小数点位置から左へ 3 桁ごとにコンマを挿入し、小数点右側には 2 桁をとります。たとえば 3,510.92 のようになります。

    2

    小数点位置から左へ 3 桁ごとの位置にはコンマを挿入しません。また、小数点右側には 4 桁をとります。たとえば 4235.9819 のようになります。
    ms187928.note(ja-jp,SQL.90).gifメモ :
    money 型、または smallmoney 型から文字データへの変換に style 126 が指定されている場合、出力は style 値 2 と同じになります。

    次の表は、文字列入力を xml データに変換するときに使用できる style の値です。

    値

戻り値の型

data_type と同じ値が返されます。

解説

暗黙的な変換とは、CAST または CONVERT 関数を指定せずに実行される変換のことです。一方、明示的な変換には、CAST または CONVERT 関数を指定する必要があります。次の図は、SQL Server 2005 システムで提供されるデータ型に許可されている、すべての明示的および暗黙的なデータ型変換です。これには、xml、bigint、および sql_variant 型が含まれます。代入時に sql_variant 型からの暗黙的な変換は行われませんが、sql_variant 型への暗黙的な変換は行われます。

データ型変換テーブル

ms187928.note(ja-jp,SQL.90).gifメモ :
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))
ms187928.note(ja-jp,SQL.90).gifメモ :
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 型に変換されるときにもエラーが返されます。

バイナリ文字列データの変換

binary または varbinary 型データを文字型データに変換する際に、x に続けて奇数桁の値を指定すると、SQL Server では値の桁数が偶数になるよう x の後に 0 (ゼロ) が追加されます。

バイナリ データは、0 ～ 9 と A ～ F (または a ～ f) のうち 2 文字ずつのグループで構成されます。バイナリ文字列の直前には、0x が必要です。たとえば、FF を入力する場合は、「0xFF」と入力します。最大値は 8,000 バイトのバイナリ データで、各バイトが FF になります。binary 型は 16 進数データではなく、ビット パターンです。バイナリ データとして格納されたデータを 16 進数として変換および計算した場合は、正しい結果を得られないことがあります。

binary 型の長さを指定するときは、2 文字を 1 として数えます。長さ 10 を指定した場合は、2 文字のグループが 10 個格納されるので、文字数は 20 文字になります。

0x で表される空のバイナリ文字列を、バイナリ データとして格納することもできます。

例

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-04T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

以下に結果セットを示します。

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997

(1 row(s) affected)

参照

関連項目

SELECT (Transact-SQL)
システム関数 (Transact-SQL)

その他の技術情報

データ型の変換 (データベース エンジン)
ISO 8601 形式
国際化に対応した Transact-SQL ステートメントの記述

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 7 月 17 日
新しい内容 :
  • 例 G を追加。

2006 年 4 月 14 日
新しい内容 :
  • style 127 に対応するタイム ゾーン インジケータ Z の説明を追加。