資料類型轉換 (資料庫引擎)

適用于:SQL ServerAzure SQL資料庫Azure SQL 受控執行個體Azure SynapseAnalytics AnalyticsPlatform System (PDW)

在下列情況中可以轉換資料類型:

  • 將一個物件的資料移到另一個物件、與另一個物件的資料作比較,或與另一個物件的資料結合時,可能需要將資料從一個物件的資料類型轉換成其他物件的資料類型。
  • 當 Transact-SQL 結果資料行、傳回碼或輸出參數中的資料移至程式變數時,必須將資料從SQL Server系統資料類型轉換成變數的資料類型。

當您在應用程式變數與SQL Server結果集資料行、傳回碼、參數或參數標記之間轉換時,支援的資料型別轉換是由資料庫 API 所定義。

隱含及明確轉換

資料類型可以隱含或明確地轉換。

使用者看不到隱含轉換。 SQL Server會自動將資料從某個資料類型轉換成另一個資料類型。 例如,如果將 smallintint做比較,會先將 smallint 隱含轉換成 int再繼續比較。

GETDATE() 會隱含轉換成日期樣式 0。 SYSDATETIME() 會隱含轉換成日期樣式 21。

明確轉換使用 CAST 或 CONVERT 函數。

CAST 和 CONVERT 函數會將數值 (本機變數、資料行或其他運算式) 轉換成另一個資料類型。 例如,下列 CAST 函數會將 $157.27 的數值轉換成 '157.27' 的字元字串:

CAST ( $157.27 AS VARCHAR(10) )  

如果您想要 Transact-SQL 程式碼符合 ISO,請使用 CAST 而非 CONVERT。 要善用 CONVERT 的樣式功能,可不使用 CAST 而改用 CONVERT。

下圖顯示SQL Server系統提供資料類型允許的所有明確和隱含資料類型轉換。 這些包括 xmlbigintsql_variant。 從 sql_variant 資料類型進行指派時,不可使用隱含轉換,但可以隱含轉換成 sql_variant

資料類型轉換資料表

雖然上圖顯示了 SQL Server 中允許的所有明確及隱含轉換,但是它並未指出轉換的結果資料類型。 當 SQL Server 執行明確轉換時,陳述式本身便會判斷結果的資料類型。 針對隱含轉換,指派陳述式 (例如設定變數值或將值插入資料行) 會產生變數宣告或資料行定義所定義的資料類型。 針對比較運算子或其他運算式,結果資料類型會取決於資料類型優先順序的規則。

例如,下列指令碼會定義 varchar 類型的變數、將 int 類型的值指派給變數,然後使用字串選取變數的串連。

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.'

int1 會轉換成 varchar,因此 SELECT 陳述式會傳回 1 is a string. 值。

下列範例會顯示相似的指令碼,但是會改用 int 變數:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.'

在此情況下,SELECT 陳述式會擲回下列錯誤:

Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ' is not a string.' to data type int.

為了評估運算式 @notastring + ' is not a string.',SQL Server 會遵循資料類型優先順序規則,在計算運算式的結果前完成隱含轉換。 因為 int 具有比 varchar 更高的優先順序,所以 SQL Server 會嘗試將字串轉換成整數,但由於此字串無法轉換成整數,因此會失敗。 若運算式提供可進行轉換的字串,則陳述式會成功,如下列範例所示:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

在此情況下,字串 1 便可轉換成整數值 1,因此此 SELECT 陳述式即會傳回 2 的值。 請注意,在提供的資料類型為整數時,+ 運算子會變成加法,而非串連。

資料類型轉換行為

當您將某個SQL Server物件的資料類型轉換成另一個物件時,不支援某些隱含和明確的資料類型轉換。 例如,nchar 值無法轉換成 image 值。 您只可使用明確轉換將 nchar 轉換成 binary;系統並不支援隱含轉換成 binary。 但 nchar 可以明確或隱含轉換成 nvarchar

下列主題描述其對應資料類型所展示的轉換行為:

使用 OLE Automation 預存程序轉換資料類型

因為SQL Server使用 Transact-SQL 資料類型,而 OLE Automation 使用 Visual Basic 資料類型,所以 OLE Automation 預存程式必須轉換它們之間傳遞的資料。

下表描述SQL Server至 Visual Basic 資料類型轉換。

SQL Server 資料類型 Visual Basic 資料類型
charvarchartextnvarcharntext String
decimalnumeric String
bit 布林值
binaryvarbinaryimage 一維 Byte() 陣列
int Long
smallint 整數
tinyint 位元組
float Double
real Single
moneysmallmoney 貨幣
datetimesmalldatetime 日期
設成 NULL 的任何類型 Variant 設為 Null

所有單一SQL Server值都會轉換成單一 Visual Basic 值,但binaryVarbinaryimage值除外。 這些值會轉換成 Visual Basic 中的一維 Byte () 陣列。 此陣列有位元組 ( 0 到長度1**) ** 的範圍,其中length是 SQL Server binaryVarbinaryimage值中的位元組數目。

這些是從 Visual Basic 資料類型轉換成SQL Server資料類型。

Visual Basic 資料類型 SQL Server 資料類型
LongIntegerByteBooleanObject int
DoubleSingle float
貨幣 money
日期 datetime
字元數不超過 4000 個的 String varchar/nvarchar
多於 4000 個字元的 String text/ntext
位元組數不超過 8000 的一維 Byte() 陣列 varbinary
位元組數超過 8000 的一維 Byte() 陣列 image

請參閱

OLE Automation 預存程序 (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)
資料類型 (Transact-SQL)
COLLATE (Transact-SQL)