SQL Server CAST 函數數據類型轉換實戰速查

CAST 基礎語法

CAST ( expression AS data_type [ ( length ) ] )

• ANSI SQL 標準函數，跨數據庫可移植性優於 CONVERT。
• 不支持 style 參數，日期時間格式化需用 CONVERT 或 FORMAT (2012+)。
• 轉換失敗會報錯，SQL 2012+ 可用 TRY_CAST 返回 NULL 而不報錯。

常用轉換示例

1. 數值 → 字符串

DECLARE @num INT = 123;
SELECT CAST(@num AS VARCHAR(10));     -- '123'
SELECT CAST(@num AS NVARCHAR(20));    -- N'123'
SELECT CAST(@num AS CHAR(5));         -- '123  ' (右填空格，定長)

2. 字符串 → 數值

DECLARE @str VARCHAR(20) = '456';
SELECT CAST(@str AS INT);       -- 456
SELECT CAST(@str AS BIGINT);    -- 456
SELECT CAST(@str AS DECIMAL(10,2)); -- 456.00

-- 非數字字符串轉換會報錯
-- SELECT CAST('abc' AS INT);  -- 錯誤: Conversion failed

3. 日期時間 ↔ 字符串 (需配合 CONVERT 格式化)

DECLARE @dt DATETIME = GETDATE();

-- CAST 只能轉默認格式 (同 CONVERT style 0)
SELECT CAST(@dt AS VARCHAR(30));  -- 'Jan 15 2024 10:30AM'

-- 精確控制格式必須用 CONVERT
SELECT CONVERT(VARCHAR(10), @dt, 120);  -- '2024-01-15'
SELECT CONVERT(VARCHAR(19), @dt, 120);  -- '2024-01-15 10:30:45'

-- SQL 2012+ 可用 FORMAT (更靈活但較慢)
SELECT FORMAT(@dt, 'yyyy-MM-dd HH:mm:ss');

4. 字符串長度截取/補齊

SELECT CAST('Hello World' AS VARCHAR(5));   -- 'Hello' (截斷)
SELECT CAST('Hi' AS CHAR(10));              -- 'Hi        ' (右補空格)
SELECT CAST('Hi' AS NCHAR(10));             -- N'Hi        ' (Unicode 定長)

5. 二進制/十六進制轉換

SELECT CAST(0x48656C6C6F AS VARCHAR(20));  -- 'Hello' (0x 十六進制 → 字符串)
SELECT CAST('Hello' AS VARBINARY(20));     -- 0x48656C6C6F (字符串 → 二進制)

CAST vs CONVERT vs TRY_CAST vs FORMAT 對比

實戰：安全轉換髒數據 (TRY_CAST)

-- 原始數據表 RawData(DirtyValue VARCHAR(50))
-- 需求：轉為 INT，無效值設為 0

-- 方法 1: CASE + ISNUMERIC (ISNUMERIC 有誤判，如 '1e2','-$' 也返回 1)
SELECT CASE WHEN ISNUMERIC(DirtyValue) = 1 THEN CAST(DirtyValue AS INT) ELSE 0 END
FROM RawData;

-- 方法 2: TRY_CAST (推薦，2012+)
SELECT ISNULL(TRY_CAST(DirtyValue AS INT), 0) AS CleanValue
FROM RawData;

-- 方法 3: TRY_CONVERT 日期解析
SELECT ISNULL(TRY_CONVERT(DATE, DirtyValue, 120), '1900-01-01') AS CleanDate
FROM RawData;

隱式轉換陷阱與性能殺手

-- 殺手寫法：導致索引失效，全表掃描
SELECT * FROM Orders WHERE CAST(OrderDate AS DATE) = '2024-01-15';

-- 正確寫法：利用範圍查詢保持 SARGable
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-15' AND OrderDate < '2024-01-16';

常用類型轉換速查表