SQL 型別轉換與 TEXT/IMAGE 大物件處理全攻略

SQL Server 型別轉換與大物件操作完整指南

資料型別轉換是 ETL、報表、跨系統整合的日常操作。SQL Server 提供 CONVERT、CAST、TRY_CAST 等多種轉換方式，並支援 TEXT、IMAGE、NTEXT 等大物件型別的專用函數（註：新版本建議改用 VARCHAR(MAX)、VARBINARY(MAX)）。

CONVERT 函數完整語法

CONVERT(target_type [ ( length ) ], expression [, style ])

• target_type：目標資料型別（如 INT、VARCHAR、DATETIME）
• length：字串/二進位型別的長度，最大 8000 (MAX 為 2^31-1)，預設 30
• style：日期/時間轉字串時的格式代碼

常用日期轉字串 Style 代碼表

型別轉換最佳實踐

安全轉換：TRY_CAST / TRY_CONVERT (SQL 2012+)

-- 轉換失敗返回 NULL 而非報錯
SELECT TRY_CAST('abc' AS INT)      -- NULL
SELECT TRY_CONVERT(DATE, '2024-13-01')  -- NULL

-- 配合 CASE 處理異常資料
SELECT 
    raw_value,
    CASE 
        WHEN TRY_CAST(raw_value AS INT) IS NULL THEN 'Invalid'
        ELSE 'Valid'
    END AS validation
FROM staging_table

隱含轉換陷阱

-- 避免：WHERE varchar_col = 123  (導致索引失效)
-- 建議：WHERE varchar_col = '123'  或  WHERE CAST(varchar_col AS INT) = 123

TEXT / IMAGE / NTEXT 大物件處理函數

注意： TEXT、NTEXT、IMAGE 在未來版本將被移除，新開發請使用 VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)。

大物件操作實戰範例

使用 TEXTPTR 搭配 READTEXT/UPDATETEXT/WRITETEXT

-- 取得指標
DECLARE @ptr VARBINARY(16)
SELECT @ptr = TEXTPTR(doc_content) FROM documents WHERE doc_id = 1

-- 驗證指標
IF TEXTVALID('documents.doc_content', @ptr) = 1
BEGIN
    -- 讀取前 100 位元組
    READTEXT documents.doc_content @ptr 0 100
    
    -- 附加寫入
    UPDATETEXT documents.doc_content @ptr NULL 0 'New content'
END

現代化寫法：VARCHAR(MAX) 直接操作

-- 新版本直接當作一般字串處理
UPDATE documents 
SET doc_content = doc_content + ' appended text'
WHERE doc_id = 1

-- 部分更新
UPDATE documents
SET doc_content = STUFF(doc_content, 1, 10, 'New header')
WHERE doc_id = 1

型別轉換效能建議

• 儘量在應用層完成轉換，減少資料庫 CPU 壓力
• WHERE 條件避免對欄位做轉換（導致 SARGable 失效）
• 大量資料轉換考慮分批處理，避免交易記錄暴增
• 使用 TRY_CAST 進行資料品質檢核，配合 TRY...CATCH 錯誤處理