搜尋本站文章

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() 函數







參考資料

TRY_CAST (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh974669

CAST 和 CONVERT (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187928

TRY_CONVERT (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh230993

轉換函數 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh231076

CASE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms181765.aspx