資料類型轉換 (資料庫引擎)
適用於:sql Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Platform System (PDW) SQL 分析端點Microsoft網狀架構倉儲中的 Microsoft Fabric SQL DatabaseMicrosoft網狀架構
在下列情況中可以轉換資料類型:
- 將一個物件的資料移到另一個物件、與另一個物件的資料作比較,或與另一個物件的資料結合時,可能需要將資料從一個物件的資料類型轉換成其他物件的資料類型。
- 當 Transact-SQL 結果資料行、傳回碼或輸出參數的資料移至程式變數時,該資料必須從 SQL Server 系統資料類型,轉換成變數的資料類型。
在應用程式變數與 SQL Server 結果集資料行、傳回程式碼、參數或參數標記之間轉換時,所支援的資料類型轉換是由資料庫 API 定義。
隱含及明確轉換
資料類型可以隱含或明確地轉換。
使用者看不到隱含轉換。 SQL Server 會自動將資料從一種類型轉換成其他資料類型。 例如,如果將 smallint 與 int做比較,會先將 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 系統提供之資料類型所能使用的所有明確與隱含資料類型轉換。 這些包括 xml、bigint 和 sql_variant。 從 sql_variant 資料類型進行指派時,不可使用隱含轉換,但可以隱含轉換成 sql_variant。
雖然上圖顯示了 SQL Server 中允許的所有明確及隱含轉換,但是它並未指出轉換的結果資料類型。
- 當 SQL Server 執行明確轉換時,陳述式本身便會判斷結果的資料類型。
- 針對隱含轉換,指派陳述式 (例如設定變數值或將值插入資料行) 會產生變數宣告或資料行定義所定義的資料類型。
- 針對比較運算子或其他運算式,結果資料類型會取決於資料類型優先順序的規則。
例如,下列指令碼會定義 varchar 類型的變數、將 int 類型的值指派給變數,然後使用字串選取變數的串連。
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.'
1
的 int 值會轉換成 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。
下列文章說明其對應資料類型所表現的轉換行為:
- binary 和 varbinary
- datetime2
- money 和 smallmoney
- bit
- datetimeoffset
- smalldatetime
- char 和 varchar
- decimal 和 numeric
- sql_variant
- date
- float 和 real
- time
- datetime
- int、bigint、smallint 和 tinyint
- uniqueidentifier
- xml
- json
使用 OLE Automation 預存程序轉換資料類型
因為 SQL Server 使用 Transact-SQL 資料類型,而 OLE Automation 使用 Visual Basic 資料類型,所以 OLE Automation 預存程序必須轉換在兩者之間傳遞的資料。
下表描述 SQL Server 轉 Visual Basic 資料類型轉換。
SQL Server 資料類型 | Visual Basic 資料類型 |
---|---|
char、varchar、text、nvarchar、ntext | String |
decimal、numeric | String |
bit | 布林值 |
binary、varbinary、image | 一維的 Byte() 陣列 |
int | Long |
smallint | 整數 |
tinyint | 位元組 |
float | Double |
real | Single |
money、 smallmoney | 貨幣 |
datetime、smalldatetime | 日期 |
設定為 NULL 的任何項目 |
Variant 設為 Null |
所有單一的 SQL Server 值皆會轉換成單一的 Visual Basic 值,但不包括 binary、varbinary 及 image 值。 這些值會在 Visual Basic 中轉換成一維的 Byte()
陣列。 此陣列的範圍為 Byte( 0 to length 1)
,其中 length 是 SQL Server binary、varbinary 或 image 值中的位元組數目。
這些是從 Visual Basic 資料類型到 SQL Server 資料類型的轉換。
Visual Basic 資料類型 | SQL Server 資料類型 |
---|---|
Long、Integer、Byte、Boolean、Object | int |
Double、Single | float |
貨幣 | money |
日期 | datetime |
字元數不超過 4,000 個的字串 | varchar/nvarchar |
多於 4,000 個字元的字串 | text/ntext |
位元組數不超過 8,000 的一維 Byte() 陣列 |
varbinary |
位元組數超過 8,000 的一維 Byte() 陣列 |
image |