CAST 和 CONVERT (Transact-SQL)
在 SQL Server 2008 R2 中，將運算式從某個資料類型轉換成另一個資料類型。
.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 的整數運算式。如果 style 是 NULL，就會傳回 NULL。此範圍由 data_type 決定。如需詳細資訊，請參閱＜備註＞一節。
傳回類型
傳回轉譯成 data_type 的 expression。
備註
日期和時間樣式
當 expression 是日期或時間資料類型時， style 就可以是下表所列的其中一個值。其他值則當做 0 處理。SQL Server 利用科威特演算法來支援阿拉伯文樣式的日期格式。
不含世紀 (yy) (1) |
含世紀 (yyyy) |
標準 |
輸入/輸出 (3) |
|---|---|---|---|
- |
0 或 100 (1,2) |
預設值 |
mon dd yyyy hh:miAM (或 PM) |
1 |
101 |
U.S. |
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 |
USA |
mm-dd-yy |
11 |
111 |
JAPAN |
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) |
具有時區 Z 的 ISO8601。 |
yyyy-mm-ddThh:mi:ss.mmmZ (無空格) |
- |
130 (1,2) |
回曆 (5) |
dd mon yyyy hh:mi:ss:mmmAM |
- |
131 (2) |
回曆 (5) |
dd/mm/yy hh:mi:ss:mmmAM |
1 這些樣式值會傳回不具決定性的結果。其中包括所有 (yy) (不含世紀) 樣式和 (yyyy) (含世紀) 樣式的子集。
2 預設值 (style0 或 100、9 或 109、13 或 113、20 或 120 及 21 或 121) 一律會傳回世紀 (yyyy)。
3 當轉換成 datetime 時輸入；當轉換成字元資料時輸出。
4 專為了 XML 而設計。如果是從 datetime 或 smalldatetime 轉換成字元資料，輸出格式會符合上表的描述。
5 回歷是含有多種變體的一種日曆系統。SQL Server 使用科威特演算法。
.gif "重要事項") 重要事項 |
|---|
根據預設，SQL Server 會根據截止年份 2049 來解譯兩位數的年份。也就是說，兩位數年份 49 會解譯為 2049，而兩位數年份 50 會解譯成 1950。許多用戶端應用程式 (如以 Automation 物件為基礎的用戶端應用程式) 都是以 2030 為截止年份。SQL Server 提供了 two digit year cutoff 組態選項，用來變更 SQL Server 所用的截止年份，可讓日期有一致的處理方式。我們建議您指定四位數的年份。 |
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 轉換成字元資料時，包括秒或毫秒的樣式會在這些位置顯示零。當您從 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 (預設值) |
小數點左側並不會每隔三位數加一個逗號，小數點右側有兩個位數；如 4235.98。 |
1 |
小數點左側每隔三位數加一個逗號，小數點右側有兩位數；如 3,510.92。 |
2 |
小數點左側並不會每隔三位數加一個逗號，小數點右側有四個位數；如 4235.9819。 |
126 |
轉換成 char(n) 或 varchar(n) 時，相當於樣式 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 必須由偶數個十六進位數字 (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f) 所構成。如果 style 設定為 1，字元 0x 就必須是運算式中的前兩個字元。如果此運算式含有奇數個字元，或者其中任何字元無效，就會引發錯誤。 如果已轉換之運算式的長度超過 data_type 的長度，結果就會向右截斷。 大於已轉換結果之固定長度的 data_types 會在結果的右邊加入零。 如果 data_type 是字元類型，此運算式就必須是二進位運算式。每個二進位字元都會轉換成兩個十六進位字元。如果已轉換之運算式的長度超過 data_type 的長度，就會向右截斷。 如果 data_type 是固定大小的字元類型，而且已轉換之結果的長度小於 data_type 的長度，就會在已轉換之運算式的右邊加入空格，以便維持偶數個十六進位數字。 若為 style 1，就會在已轉換之結果的左邊加入字元 0x。 |
隱含的轉換
隱含的轉換是不需要指定 CAST 或 CONVERT 函數，便能夠進行的轉換。明確的轉換是需要指定 CAST 或 CONVERT 函數，才能進行的轉換。下圖顯示 SQL Server 系統提供的資料類型所能使用的所有明確和隱含的資料類型轉換。其中包括 xml、bigint 和 sql_variant。在指派時，沒有從 sql_variant 資料類型進行的隱含轉換，但有轉換成 sql_variant 的隱含轉換。
.gif "資料類型轉換資料表")
當您在 datetimeoffset 與字元類型 char、varchar、nchar 和 nvarchar 之間轉換時，轉換過的時區時差部分的 HH 和 MM 應該永遠是兩位數，例如 -08:00。
[!附註]
由於 Unicode 資料使用的位元組數目一律是偶數，因此，在 binary 或 varbinary 和 Unicode 支援的資料類型之間轉換時，要特別小心。例如，下列轉換不會傳回 41 的十六進位值；它會傳回 4100：SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)。
大數值資料類型
大數值資料類型會表現出與較小對應項目相同的隱含和明確轉換行為，明確地說，便是 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 Common Language Runtime (CLR) 使用者定義型別的資訊，請參閱＜對使用者自訂類型執行作業＞。如需有關如何轉換 xml 資料類型的詳細資訊，請參閱＜產生 XML 執行個體＞。
XML 資料類型
當您將 xml 資料類型明確或隱含地轉換為字串或二進位資料類型時，xml 資料類型的內容會根據一組規則來序列化。如需有關這些規則的資訊，請參閱＜XML 資料的序列化＞。如需有關如何從 XML 轉換為 CLR 使用者自訂類型的資訊，請參閱＜對使用者自訂類型執行作業＞。如需有關如何從其他資料類型轉換成 xml 資料類型的詳細資訊，請參閱＜產生 XML 執行個體＞。
text 和 image 資料類型
不支援 text 和 image 資料類型進行自動資料類型轉換。您可以將 text 資料明確地轉換成字元資料，將 image 資料轉換成 binary 或 varbinary，但最大長度是 8000 位元組。如果您試圖進行不正確的轉換，例如將包括字母的字元運算式轉換成 int，SQL Server 會傳回一則錯誤訊息。
輸出定序
當 CAST 或 CONVERT 的輸出是字元字串，輸入也是字元字串時，輸出的定序和定序標籤與輸入相同。如果輸入不是字元字串，輸出會使用預設的資料庫定序及強制預設的定序標籤。如需詳細資訊，請參閱＜定序優先順序 (Transact-SQL)＞。
若要將不同的定序指派給輸出，請將 COLLATE 子句套用至 CAST 或 CONVERT 函數結果運算式上。例如：
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 不保證在 SQL Server 的各個版本之間，將 decimal 或 numeric 數值資料轉換成 binary 會有相同的結果。
下列範例會顯示因太小而無法顯示的結果運算式。
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e
JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID >5;
以下為結果集：
FirstName LastName Title Sick Leave
--------- ------------- ---------- -----------
Ken Sanchez NULL *
Terri Duffy NULL *
Roberto Tamburello NULL *
Rob Walters NULL *
Gail Erickson Ms. *
(5 row(s) affected)
當您轉換小數位數不同的資料類型時，有時會截斷結果值，有時會捨入結果值。下表說明這個行為。
來源 |
目的地 |
行為 |
|---|---|---|
numeric |
numeric |
捨入 |
numeric |
int |
截斷 |
numeric |
money |
捨入 |
money |
int |
捨入 |
money |
numeric |
捨入 |
float |
int |
截斷 |
float |
numeric |
捨入 如果您將使用科學記號標記法的 float 值轉換成 decimal 或 numeric，就會限制為只有 17 個有效位數的值。有效位數超過 17 的任何值都會捨入為零。 |
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 會傳回一則錯誤訊息。當空字串 (" ") 轉換成 numeric 或 decimal 時，SQL Server 也會傳回一則錯誤。
某些日期時間轉換在 SQL Server 2005 和更新版本中不具決定性
在 SQL Server 2000 中，字串對日期和時間轉換會標示為具決定性。不過，這對於下表所列出的樣式並不適用。對於這些樣式而言，轉換取決於語言設定。SQL Server 2005 及更新的版本會將這些轉換標示為不具決定性。
下表列出字串對日期時間轉換不具決定性的樣式。
100 以下的所有樣式 1 |
106 |
107 |
109 |
113 |
130 |
1 樣式 20 和 21 例外
範例
A. 使用 CAST 和 CONVERT
每個範例都會擷取標價第一位數是 3 的產品名稱，且會將它們的 ListPrice 轉換成 int。
-- Use CAST
USE AdventureWorks2008R2;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO
-- Use CONVERT.
USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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. 搭配 LIKE 子句使用 CAST
下列範例會將 money 資料行 SalesYTD 轉換成 int，再轉換成 char(20) 資料行，以便能夠搭配 LIKE 子句來使用。
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person p JOIN Sales.SalesPerson s ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO
以下為結果集：
FirstName LastName SalesYTD SalesPersonID
---------------- ------------------- ---------------- -------------
Tsvi Reiter 2811012.7151 279
Syed Abbas 219088.8836 288
Rachel Valdez 2241204.0424 289
(3 row(s) affected)
F. 搭配具類型的 XML 來使用 CONVERT 或 CAST
下列範例會顯示如何利用 CONVERT 來轉換成 XML 類型 (使用＜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. 搭配 datetime 資料使用 CAST 和 CONVERT
下列範例會顯示目前的日期和時間、使用 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', 2) AS 'Style 2, character to binary'
以下為結果集：
Style 2, character to binary
----------------------------------
0x4E616D65
(1 row(s) affected)