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(zh-tw,SQL.90).gif附註：
    在所描述之與 CONVERT 目標資料類型的組合中，SQL Server 支援這個主題列出的樣式。不支援所有其他樣式和組合。請不要使用任何不支援的樣式。使用不支援的樣式或不支援的樣式與目標資料類型的組合，可能會傳回錯誤或無法信賴的結果。不同版本的 SQL Server 中，不保證都有這些結果。

    SQL Server 利用科威特演算法來支援阿拉伯文樣式的日期格式。

    在下表中，左側的兩個資料行代表將 datetime 或 smalldatetime 資料轉換成字元資料的 style 值。style 值加 100，便能得出包括世紀的四位數年份 (yyyy)。

    不含世紀 (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/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

    USA

    mm-dd-yy

    11

    111

    JAPAN

    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)

    具有時區 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 2005 使用科威特演算法。

    ms187928.note(zh-tw,SQL.90).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 資料類型長度來截斷不需要的日期部分。

    下表顯示將 float 或 real 轉換成字元資料時，所能使用的 style 值。

    值 輸出

    0 (預設值)

    最多 6 位數。在適當時機，供科學記號標記法使用。

    1

    一律 8 位數。一律用在科學記號標記法中。

    2

    一律 16 位數。一律用在科學記號標記法中。
    ms187928.note(zh-tw,SQL.90).gif附註：
    如果指定樣式 126 以便從 float 和 real 進行轉換，輸出就相當於 style 值 2 和 1。

    下表顯示將 money 或 smallmoney 轉換成字元資料時，所能使用的 style 值。

    值 輸出

    0 (預設值)

    小數點左側並不會每隔三位數加一個逗號，小數點右側有兩個位數；如 4235.98。

    1

    小數點左側每隔三位數加一個逗號，小數點右側有兩位數；如 3,510.92。

    2

    小數點左側並不會每隔三位數加一個逗號，小數點右側有四個位數；如 4235.9819。
    ms187928.note(zh-tw,SQL.90).gif附註：
    如果指定樣式 126 以便從 money 或 smallmoney 轉換成字元資料，輸出就相當於 style 值 2。

    下表顯示將字串輸入轉換成 xml 資料時，所能使用的 style 值。

    值

傳回類型

傳回 data_type 的相同值。

備註

隱含的轉換是不需要指定 CAST 或 CONVERT 函數，便能夠進行的轉換。明確的轉換是需要指定 CAST 或 CONVERT 函數，才能進行的轉換。下圖顯示 SQL Server 2005 系統提供的資料類型所能使用的所有明確和隱含的資料類型轉換。其中包括 xml、bigint 和 sql_variant。在指派時，沒有從 sql_variant 資料類型進行的隱含轉換，但有轉換成 sql_variant 的隱含轉換。

資料類型轉換資料表

ms187928.note(zh-tw,SQL.90).gif附註：
由於 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))
ms187928.note(zh-tw,SQL.90).gif附註：
請勿試圖建構 binary 值，再將它們轉換成數值資料類型類別目錄的資料類型。SQL Server 不保證在 SQL Server 的各個版本之間，將 decimal 或 numeric 數值資料轉換成 binary 會有相同的結果。

下列範例會顯示因太小而無法顯示的結果運算式。

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 會傳回一則錯誤訊息。當空字串 (" ") 轉換成 numeric 或 decimal 時，SQL Server 也會傳回一則錯誤。

轉換二進位字串資料

當 binary 或 varbinary 資料轉換成字元資料時，且在 x 之後指定的值數目是奇數，SQL Server 便會在 x 之後加一個 0（零），使值的數目變成偶數。

二進位資料由 0-9 和 A-F 或 a-f 等字元組成，兩個字元一組。二進位字串的前面必須有 0x。例如，若要輸入 FF，請輸入 0xFF。最大值是 8000 位元組的二進位值，每一個都是 FF。binary 資料類型不是針對十六進位資料，而是針對位元模式。儲存為二進位資料的十六進位數字轉換和計算，可能不可靠。

當您指定 binary 資料類型的長度時，每兩個字元算作一個字元。長度 10 表示將輸入 10 組雙位元組。

空白二進位字串用 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. 搭配 LIKE 子句使用 CAST

下列範例會將 money 資料行 SalesYTD 轉換成 int，再轉換成 char(20) 資料行，以便能夠搭配 LIKE 子句來使用。

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. 搭配具類型的 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-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)

其他資源

資料類型轉換 (Database Engine)
ISO 8601 格式
撰寫國際性通用的 Transact-SQL 陳述式

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 7 月 17 日
新增內容：
  • 加入範例 G。

2006 年 4 月 14 日
新增內容：
  • 新增樣式 127 的 Z 時區指標描述。