德瑞克：SQL Server 學習筆記: SQL Server 2012 ：認識 TRY

2012-07-04

SQL Server 2012 ：認識 TRY_CAST 轉換函數

適用版本：SQL Server 2012。

TRY_CAST() 轉換函數
轉換運算式的資料類型；若轉換失敗，則傳回 Null。

語法：

TRY_CAST ( expression AS data_type [ ( length ) ] )

在 SSMS 管理工具上，TRY_CAST() 轉換函數卻是顯示：「不是可辨識的內建函數名稱」。
但在功能上，仍是可以正常運作。

建議：

先學習 CAST() 轉換函數的使用方式。

先參考以下的資料：
CAST 和 CONVERT (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187928

請參考以下的範例程式碼：

EX1：認識 CAST()、TRY_CAST() 轉換函數

-- CAST() 轉換函數，輸入字串：123，轉型為 int 
SELECT CAST('123' AS int) N'資料轉型為 int'
GO

-- 01_CAST() 轉換函數，輸入字串：123，轉型為 int

-- CAST() 轉換函數：輸入字串：A123，嘗試轉型為 int 
SELECT CAST('A123' AS int)
GO
/*
錯誤訊息：
訊息 245，層級 16，狀態 1，行 2
將 varchar 值 'A123' 轉換成資料類型 int 時，轉換失敗。
*/

-- 02_CAST() 轉換函數：輸入字串：A123，嘗試轉型為 int

-- TRY_CAST() 轉換函數：輸入字串：A123，嘗試轉型為 int
-- 但 TRY_CAST() 轉換函數，SSMS 管理工具卻顯示：不是可辨識的內建函數名稱
-- 若轉換失敗，則傳回 Null。
SELECT TRY_CAST('A123' AS int) N'資料轉型為 int'
GO
/*
回傳：NULL
*/

-- 03_TRY_CAST() 轉換函數：輸入字串：A123，嘗試轉型為 int

-- CAST() 轉換函數：GETDATE() 顯示目前的伺服器日期時間
SELECT GETDATE() N'尚未轉換_伺服器的日期時間',
   CAST(GETDATE() AS varchar(30)) N'CAST() 函數轉為字串',
   CONVERT(varchar(30), GETDATE(), 126) N'CONVERT() 函數轉為 ISO 8601 格式';
GO

-- CAST() 轉換函數：SYSDATETIME() 顯示目前的伺服器日期時間
SELECT SYSDATETIME() N'尚未轉換_伺服器的日期時間',
   CAST(SYSDATETIME() AS varchar(30)) N'CAST() 函數轉為字串',
   CONVERT(varchar(30), SYSDATETIME(), 126) N'CONVERT() 函數轉為 ISO 8601 格式';
GO

-- 04_CAST() 轉換函數：GETDATE()以及SYSDATETIME()

-- TRY_CAST() 轉換函數：GETDATE() 顯示目前的伺服器日期時間
-- 但卻顯示：不是可辨識的內建函數名稱
SELECT GETDATE() N'尚未轉換_伺服器的日期時間',
   TRY_CAST(GETDATE() AS varchar(30)) N'TRY_CAST() 函數轉為字串',
   CONVERT(varchar(30), GETDATE(), 126) N'CONVERT() 函數轉為 ISO 8601 格式';
GO

-- TRY_CAST() 轉換函數：SYSDATETIME() 顯示目前的伺服器日期時間
SELECT SYSDATETIME() N'尚未轉換_伺服器的日期時間',
   TRY_CAST(SYSDATETIME() AS varchar(30)) N'TRY_CAST() 函數轉為字串',
   CONVERT(varchar(30), SYSDATETIME(), 126) N'CONVERT() 函數轉為 ISO 8601 格式';
GO

-- 05_TRY_CAST() 轉換函數：GETDATE()以及SYSDATETIME()

EX2：TRY_CAST() 轉換函數：不允許從資料類型 int 明確轉換至 xml

-- TRY_CAST() 轉換函數：仍是有明確的錯誤
-- 這是因為整數是無法轉型為 XML 資料類型
SELECT TRY_CAST(4 AS xml) AS Result;
GO
/*
錯誤訊息：
訊息 529，層級 16，狀態 2，行 1
不允許從資料類型 int 明確轉換至 xml。
*/

-- 06_TRY_CAST() 轉換函數：不允許從資料類型 int 明確轉換至 xml

EX3：TRY_CAST() 轉換函數：判斷輸入的字串，是否可以轉換為數值

-- TRY_CAST() 轉換函數：輸入字串：A123，判斷輸入的字串，是否可以轉換為數值
DECLARE @cs1 varchar(100) ='A123', @cnt INT
SET @cnt=TRY_CAST(@cs1 AS int)

IF @cnt IS NOT NULL
 SELECT @cnt N'轉換結果'
ELSE
 SELECT N'轉換失敗' N'轉換結果'
GO

-- 07_TRY_CAST() 轉換函數：輸入字串：A123，判斷輸入的字串，是否可以轉換為數值

-- TRY_CAST() 轉換函數：輸入字串：123，判斷輸入的字串，是否可以轉換為數值
DECLARE @cs1 varchar(100) ='123', @cnt INT
SET @cnt=TRY_CAST(@cs1 AS int)

IF @cnt IS NOT NULL
 SELECT @cnt N'轉換結果'
ELSE
 SELECT N'轉換失敗' N'轉換結果'
GO

-- 08_TRY_CAST() 轉換函數：輸入字串：123，判斷輸入的字串，是否可以轉換為數值

-- TRY_CAST() 轉換函數，搭配使用 CASE 運算式
-- 輸入字串：A123
DECLARE @cs1 varchar(100) ='A123', @cnt INT
SET @cnt=TRY_CAST(@cs1 AS int)

SELECT 
 CASE WHEN @cnt IS NOT NULL
  THEN @cnt
  ELSE 0 
 END N'轉換結果'
GO

-- 09_TRY_CAST() 轉換函數，搭配使用 CASE 運算式

EX4：TRY_CAST() 轉換函數：搭配查詢資料表

-- TRY_CAST() 轉換函數：轉換為整數
USE Northwind
GO
SELECT  CustomerID, City, PostalCode,
 TRY_CAST(PostalCode AS int) N'PostalCode_TRY_CAST_整數'
FROM Customers
GO

-- 10_TRY_CAST() 轉換函數：轉換為整數

-- TRY_CAST() 轉換函數：轉換為整數，搭配 ISNULL() 函數
SELECT  CustomerID, City, PostalCode,
 TRY_CAST(PostalCode AS int) N'PostalCode_TRY_CAST_整數',
 ISNULL(TRY_CAST(PostalCode AS int), 0) N'PostalCode_TRY_CAST_ISNULL_整數'
FROM Customers
GO

-- 11_TRY_CAST() 轉換函數：轉換為整數，搭配 ISNULL() 函數