如何在SQL中用CAST與CONVERT進行資料型態轉換教學

你在進行SQL轉換時，常會遇到不同的資料型態。CAST與CONVERT可以幫助你正確處理這些型態。

• 你能利用CAST與CONVERT將數字、字串、XML和日期時間互相轉換，確保查詢結果正確。
• 你可以將計算結果四捨五入後再轉成整數，讓資料運算更靈活。
• 處理日期時間時，你能指定格式，像是轉成ISO 8601，方便後續操作。
• SQL Server 2012以後，這兩種方法在處理補充字元時行為一致，避免資料截斷，保證資料完整。

核心要點

• CAST語法簡單，適合快速轉換資料型態。
• CONVERT可指定格式，特別適合日期時間轉換。
• 轉換前要確認資料內容，避免型態不相容錯誤。
• 明確指定型態與長度，可提升查詢效能與安全。
• 多練習SQL轉換，能讓資料處理更正確有效率。

SQL轉換語法

當你在資料庫查詢時，常常需要進行SQL轉換。你可以用CAST或CONVERT來改變資料型態。這兩種方法語法簡單，卻有一些細節需要注意。

CAST語法

你可以用CAST來將一個值轉換成另一種資料型態。語法如下：

CAST(值 AS 目標型態)

舉例來說，當你想把字串轉成整數，可以這樣寫：

SELECT CAST('125' AS INT);

這樣會得到數字125。你也可以把日期轉成字串：

SELECT CAST(GETDATE() AS VARCHAR(20));

CAST語法在MySQL和SQL Server都支援。你只要記得「AS」後面接目標型態即可。

CONVERT語法

CONVERT也能做SQL轉換，但語法和CAST有點不同。你可以用CONVERT來指定格式，特別適合處理日期和字元集。語法如下：

• SQL Server：

  CONVERT(目標型態, 值 [, style])
  

• MySQL：

  CONVERT(值, 目標型態)
  

你可以看到，SQL Server和MySQL的參數順序不一樣。舉例來說，SQL Server中將日期轉成字串並指定格式：

SELECT CONVERT(VARCHAR(10), GETDATE(), 120);

這裡的120就是style參數，代表YYYY-MM-DD格式。MySQL則沒有style參數，但可以轉換成常見型態：

SELECT CONVERT('2024-06-01', DATE);

小提醒：在SQL Server中，style參數很重要。你可以用它來控制日期、時間、貨幣等格式。MySQL則不支援style參數。

支援型態

你在做SQL轉換時，必須知道每個資料庫支援哪些型態。以下是常見支援型態：

• MySQL 支援型態：
  • BINARY
  • CHAR()
  • DATE
  • TIME
  • DATETIME
  • DECIMAL
  • SIGNED
  • UNSIGNED
• SQL Server 支援型態：
  • INT、FLOAT、DECIMAL、CHAR、VARCHAR、NCHAR、NVARCHAR
  • DATETIME、DATE、TIME
  • BINARY、VARBINARY
  • 你還可以用style參數控制格式

你可以參考下表，快速比較兩者差異：

你在實作時，常會遇到這些情境：

• 把字串轉成數字：

  SELECT CAST('125' AS SIGNED);
  

• 把日期轉成二進位：

  DECLARE @dt datetime
  SET @dt = '1900-1-2'
  SELECT CAST(@dt AS binary(8))
  

• 把數字轉成字串再查詢：

  SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
  FROM titles
  WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
  

• 算術運算後再轉型：

  SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS Copies
  FROM titles
  

• 字串串接：

  SELECT 'The price is ' + CAST(price AS varchar(12))
  FROM titles
  WHERE price > 10.00
  

注意：如果你轉換的資料型態不相容，SQL Server會直接報錯。字串長度不夠時，也可能發生截斷或錯誤。你在寫SQL轉換時，最好先確認資料內容，避免這些問題。

你學會這些語法後，能更靈活處理資料。無論是查詢、運算還是格式轉換，都能用CAST或CONVERT完成。這是SQL轉換的基礎，也是進階資料處理的關鍵。

差異比較

語法差異

你在寫SQL時，會發現CAST和CONVERT的語法有明顯不同。CAST語法簡單，格式為 CAST(值 AS 型態)。CONVERT語法在SQL Server是 CONVERT(型態, 值, style)，在MySQL則是 CONVERT(值, 型態)。
你可以參考下表：

小提醒：SQL Server的CONVERT支援style參數，能控制日期與字串格式。MySQL則不支援style參數。

你在查詢時，語法細節會影響查詢結果與效能。例如，換行字元不同會讓SQL Server產生不同查詢計劃，無法重複使用，導致效能下降。你若用ADO.NET時，未指定參數長度，也會因字串長度不同產生多餘查詢計劃，影響快取。

適用情境

你可以根據需求選擇CAST或CONVERT：

• 你想要簡單轉型時，CAST最方便。
• 你需要控制日期或字串格式時，CONVERT（特別在SQL Server）更適合。
• 你在MySQL時，兩者功能差異不大，但CONVERT可用於字元集轉換。

建議：你在SQL Server中，若需格式化日期，優先考慮CONVERT並指定style參數。

限制

你在使用CAST和CONVERT時，會遇到一些限制：

• 不是所有型態都能互轉。你若嘗試不相容型態，SQL會報錯。
• style參數僅SQL Server支援，MySQL不支援。
• 你若未正確指定參數型態與長度，查詢計劃快取會受到干擾，效能下降。
• 你可以這樣避免效能問題：
  • 使用參數化查詢或預存程序。
  • 明確指定參數型態與長度。

小技巧：你只要正確指定參數型態與長度，就能提升查詢效能，減少快取干擾。

常見SQL轉換

數值與字串

你在資料查詢時，經常需要在數值與字串之間做SQL轉換。這樣可以讓你更靈活地處理資料。例如，你想把字串型態的數字轉成真正的數值，方便做加減乘除。你也可能需要把數值轉成字串，方便顯示或串接其他文字。

常見範例：

• 把字串轉成整數：

  SELECT CAST('100' AS INT);
  

  這樣你會得到數字100。

• 把數值轉成字串：

  SELECT CAST(250 AS VARCHAR(10));
  

  這樣你會得到字串"250"。

• 在查詢中串接文字與數值：

  SELECT '總金額：' + CAST(amount AS VARCHAR(10)) AS result
  FROM orders;
  

小提醒：如果你轉換的字串內容不是合法數字，SQL會報錯。例如 CAST('abc' AS INT) 會失敗。你可以先用 ISNUMERIC() 或 TRY_CAST()（SQL Server支援）來檢查資料。

你在處理NULL值時，轉換結果也會是NULL。例如 CAST(NULL AS VARCHAR(10)) 會得到NULL。你要特別注意這種情況，避免查詢結果出現意外的空值。

日期時間

你常常需要在日期和字串之間做SQL轉換。這對於報表、資料輸出或格式化顯示很重要。你可以用CAST或CONVERT來達成。

常見範例：

• 把日期轉成字串（SQL Server）：

  SELECT CONVERT(VARCHAR(10), GETDATE(), 120);
  

  這樣你會得到像 "2024-06-01" 這樣的格式。

• 把字串轉成日期（MySQL）：

  SELECT CAST('2024-06-01' AS DATE);
  

• 只取日期部分（SQL Server）：

  SELECT CAST(GETDATE() AS DATE);
  

注意：日期格式不正確時，SQL轉換會失敗。例如你輸入 '2024/13/01'，因為沒有第13個月，SQL會報錯。你要確保字串格式正確，才不會發生錯誤。

你在處理日期時間時，style參數很有用。SQL Server支援多種style，讓你能控制日期顯示格式。MySQL則不支援style參數，但你可以用 DATE_FORMAT() 來調整格式。

字元集

你在多語系或跨國資料庫應用時，常常需要進行字元集的SQL轉換。這能確保資料正確顯示，不會出現亂碼或資料遺失。

常見範例：

• 把字串轉成不同字元集（MySQL）：

  SELECT CONVERT('你好' USING utf8mb4);
  

  這樣可以讓你正確儲存和顯示中文。

• 把字串轉成拉丁字元集：

  SELECT CONVERT('abc' USING latin1);
  

你在字元集轉換時，可能會遇到資料遺失或顯示錯誤。業界常用以下指標來評估轉換品質：

• 文字錯誤率（WER）：計算錯誤字數與正確字詞總數的比率。數值越低，代表轉換越準確。
• 語彙基元錯誤率（TER）：評估單字、大寫、標點等的正確性，與人工標記的正確文字比較。
• 詞語自動分段標記錯誤率（WDER）：計算錯誤指派給說話者的詞語數量。數值越低，品質越好。

你可以參考以下最佳實踐，提升字元集SQL轉換的準確度：

• 設計符合輸入地區設定的介面，避免地區不符造成辨識精確度下降。
• 允許使用者重試輸入，遇到暫時性問題時能提升整體準確度與體驗。

小提醒：你在字元集轉換時，若遇到不支援的字元，SQL會自動以問號（?）或NULL取代。你要特別注意資料完整性，避免重要資訊遺失。

隱式與顯式轉換

定義

你在寫SQL時，常會遇到「隱式轉換」和「顯式轉換」。

• 隱式轉換是指資料庫自動幫你把一種型態轉成另一種型態。你不需要特別下指令，系統會自動處理。例如，你把整數和字串相加，SQL會自動把整數轉成字串。
• 顯式轉換則是你主動用CAST或CONVERT來指定要轉成哪一種型態。你可以清楚告訴資料庫怎麼處理資料。

小提醒：隱式轉換雖然方便，但有時會造成效能下降或產生錯誤。你主動使用顯式轉換，可以讓資料處理更安全。

使用時機

你在什麼時候應該用CAST或CONVERT來做顯式轉換？
以下幾種情境，你最好主動轉型，避免錯誤：

1. 索引查詢時
   當你在SQL Server中用索引查詢，如果比較運算子的左側發生型態轉換，系統會報錯。例如，你有一個整數欄位和一個VARBINARY欄位，直接把VARBINARY欄位和整數常數比較，會出現錯誤訊息（錯誤代碼10611）。
2. 解決型態不相容
   你可以用CAST或CONVERT，把右側的常數明確轉成與欄位相同的型態（如VARBINARY）。這樣，SQL就能正確比較，不會出現錯誤。
3. 提升查詢正確率
   當你明確指定型態後，索引查詢會順利執行。這證明你主動用CAST或CONVERT，可以有效降低因隱式轉換導致的資料錯誤。

建議：你在寫查詢時，遇到型態不同的欄位與常數比較，請主動用CAST或CONVERT。這樣能提升查詢效能，也能避免不必要的錯誤發生。你養成這個習慣，資料處理會更精準、更安全。

錯誤與實務建議

型態不相容

你在進行SQL轉換時，最常遇到的錯誤就是型態不相容。當你嘗試將字串轉成日期，但字串內容不是有效日期格式，資料庫會直接報錯。例如，你執行 CAST('abc' AS DATE)，系統會提示轉換失敗。你也可能把文字欄位轉成數字，若內容有非數字字元，同樣會出現錯誤訊息。這些錯誤會中斷查詢，讓你無法取得正確資料。

提醒：你在轉換前，最好先檢查資料內容。你可以用 ISNUMERIC() 或 TRY_CAST()（SQL Server）來預先過濾不合格的資料，減少錯誤發生。

截斷與NULL

你在SQL轉換時，還會遇到資料截斷或NULL值問題。當你把長字串轉成短型態（如 VARCHAR(5)），超過長度的部分會被截斷，資料可能遺失。如果你轉換的值為NULL，結果也會是NULL。你在查詢或報表時，這些空值可能造成統計不正確或顯示異常。

• 常見情境：
  • 字串長度不足導致截斷
  • 轉換NULL值時結果仍為NULL
  • 日期格式錯誤導致NULL

小技巧：你可以用 LEN() 或 CHAR_LENGTH() 先檢查字串長度，避免截斷。你也能用 ISNULL() 或 COALESCE() 處理NULL值，確保查詢結果完整。

最佳實踐

你想提升SQL轉換的正確率與效能，可以參考以下建議：

• 你應該主動指定目標型態與長度，減少隱式轉換帶來的效能損失。
• 你可以利用統計資料（如資料分佈、索引基數、資料量）來判斷最佳轉換方式。這些資訊有助於資料庫優化器選擇更有效率的查詢路徑。
• 你可以觀察執行計劃，了解SQL語句實際執行過程，進一步調整轉換策略。
• 你能結合日誌與分析工具（如Dr.Elephant、火焰圖、Prometheus）監控SQL執行情況，發現潛在問題並即時修正。

建議：你在設計查詢時，養成檢查資料內容、明確指定型態、善用統計與監控工具的習慣。這樣能大幅降低錯誤率，讓SQL轉換更安全、效能更好。

你在選擇CAST或CONVERT時，請根據實際需求做判斷。

• 需要簡單轉型時，建議你用CAST。
• 想控制格式或處理日期，CONVERT會更適合。

多練習SQL轉換，熟悉常見錯誤與最佳實踐。你能提升資料處理能力，讓查詢更正確、更有效率。

FAQ

CAST 和 CONVERT 有什麼不同？

你用 CAST 可以簡單轉型。你用 CONVERT 可以指定格式。SQL Server 支援 style 參數，MySQL 沒有這個功能。

什麼時候要用顯式轉換？

你遇到型態不相容時，請主動用 CAST 或 CONVERT。這樣可以避免錯誤，也能提升查詢效能。

轉換失敗會發生什麼事？

你轉換失敗時，SQL 會報錯或回傳 NULL。你可以用 TRY_CAST()（SQL Server）來避免查詢中斷。

可以把所有型態互相轉換嗎？

你不能把所有型態互轉。例如，字串內容不是數字時，轉成整數會失敗。你要先檢查資料內容。

如何避免資料截斷？

你轉換字串時，請確認目標型態長度足夠。你可以用下表參考：