SQL Server 內建轉換函式介紹

SQL Server 在表的欄位中使用資料型別來儲存特定型別的值，比如數字、日期、或文字。資料型別也會在函式中被使用，比如一些數學表示式。

但是在使用資料型別時有一個問題，就是通常它們之間的相容性不是很好。而型別轉換函式可以讓它們更好的相容。

雖然有時一些型別可以自動（隱式地）轉換成另外一種型別，但在其他情況下，SQL 需要提供更好的方案。所以在這些情況下，就要使用 CAST 和 CONVERT 函式來解決問題了。

如果你對 SQL 函式還不是很熟悉，那麼我會建議你先去看一看 SQL Server 內建函式介紹。

本教程中所有的例子均是基於 Microsoft SQL Server Management Studio 和 AdventureWorks2012 資料庫的。你可以通過一些免費的工具來開始學習，參看我編寫的 SQL Server 入門教程 這篇指南。

使用 Cast 和 Convert 轉換函式從一種資料型別轉換成另一種

在以前的文章中，我談到過資料型別及其在 SQL Server 中的角色。SQL Server 使用資料型別是有一些原因的，其中一種就是協助計算表示式。但是並非所有的資料都使用了正確的資料型別。在這些情況下，在一個計算結果被確定之前所用到的一個或者更多的值必須轉換成一種常見的型別。

日期既可以顯式又可以隱式地從一種型別轉換到另一種。隱式的資料型別轉換是自然而然發生的；反之，顯式的資料型別轉換則會在你使用 CAST 和 CONVERT 轉換函式時發生。

隱式資料轉換

隱式轉換是指不使用 CAST 或 CONVERT 函式所自然而然發生的那些轉換。並非所有的值都可以隱式地轉換成另一種型別。下面的圖表顯示了可以為我們之前提到過的常用資料型別進行隱式轉換的型別：

允許隱式資料型別轉換 （這是上面圖表的說明，應該放到上一段）

請注意這個圖表顯式的只是可能可以進行隱式轉換的型別，其並不能保證一種型別中所有的值都一定可以轉換成另一種。比如，VARCHAR 型別的值 ‘The car broke down’永遠無法轉換成一種正確的 DATETIME 型別。

以下為當我們使用百分比以及 INT 資料型別時發生隱式資料轉換的一個例子。

在這個例子中，我們用標準成本（Standard cost）乘以數量（Quantity）。一個是 SMALLINT 值，它沒有小數位，而另一個是 MONEY 型別。

SELECT P.Name,
       I.Quantity,
       P.StandardCost,
       I.Quantity * P.StandardCost as TotalCost
FROM   Production.ProductInventory I
       INNER JOIN Production.Product P
       ON P.ProductID = I.ProductID
WHERE  P.StandardCost > 0.00

當你檢視結果時你會發現總成本（total cost）是有小數位的嗎？

隱式轉換結果 （上圖的說明）

在這種情況下，在 TotalCost 被計算出之前先將 Quantity 轉換成了一個 MONEY 資料型別。

你可能會奇怪，為什麼是 Quantity 轉換成了 MONEY 型別，而不是 StandardCost 轉換成 SMALLINT。

其原因是，將值從一種資料型別轉換成另一種的順序是通過型別優先順序來決定的。

資料型別優先順序決定了隱式資料型別轉換髮生時的方向或順序。以下為我們之前提到的常見資料型別的優先順序順序：

1. DATETIME （最高階）
2. FLOAT
3. DECIMAL
4. INT
5. BIT
6. NVARCHAR
7. VARCHAR （最低階）

注：有超過 30 種以上被支援的資料型別，官方的列表更長。

較低優先順序的資料型別將會嘗試轉換為更高優先順序的型別，而不是從相反方向轉換。

思考

SELECT 100 * .5

這個語句返回的值是 50.0。其原因是 .5 是一個較高優先順序的值，因此 SQL 會將一個 INT 型別的值 100 轉換成較高的優先順序。由於從 100 轉換為 100.00 是一個被允許的隱式轉換，這個過程不會發生任何錯誤。

現在來思考

SELECT 'Today is ' + GETDATE()

我們試圖取得一個類似於 ‘Today is 2015-07-02 08:01:54.932’ 這樣的結果，但該語句返回了以下錯誤：

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

你可能會認為，這個語句返回一個將當前日期和時間轉換為一個文字值的結果並不會很難，但由於 DATETIME 比文字值的優先順序更高，所以 SQL 試圖將一個文字值轉換為一個日期。

‘Today is ‘ 不是一個有效的日期，SQL 不能正確從日曆中識別它，因此引發了一個錯誤。

有跡象表明，一個值是否可以隱式的轉換為一個資料型別實際上是由於兩個因素決定的：

1. 一個值是否可以從一種資料型別轉換為另一種？隱式轉換並不支援所有的資料型別，因此需要去了解哪些組合可以正確的工作，最重要的是理解其原理。
2. 資料型別優先順序是什麼？如果一個值的資料型別已經處於較高的優先順序，它不會被隱式轉換為較低優先順序的型別。

使用 CAST 和 CONVERT 進行顯示地型別轉換

在一個表示式中並不能總是將各種資料型別結合在一起使用而不導致任何錯誤。

當發生這種情況時，我們需要顯式地將一種資料型別轉換為另一種，以避免錯誤。

以下為一個處理日期的例子。假設我們想要查詢返回有關員工生日的文字。

如果我們試圖執行以下命令，將會失敗：

SELECT P.FirstName + ' ' + P.LastName,
       'Birth Date ' + E.BirthDate
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

這將返回一個錯誤：

Msg 402, Level 16, State 1, Line 1 The data types varchar and date are incompatible in the add operator.

問題是由於這個表示式引起的

'Birth Date'+ E.BirthDate

BirthDate 欄位的資料型別是 DateTime。我們要讓文字與日期結合工作。為了解決這個問題，我們必須首先將 DateTime 資料型別轉換為一個文字值。

我們可以使用 CAST 語句將 BirthDate 轉換為一個 VARCHAR 值。這條語句執行後不會報任何錯誤

SELECT P.FirstName + ' ' + P.LastName,
       'Birth Date ' + CAST(E.BirthDate as VARCHAR)
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

我們可以使用 CAST 和 CONVERT 兩個命令做到這一點。

CAST 和 CONVERT 之間的主要區別是，CONVERT 還允許你定義轉換後的值的格式。例如，將 DATETIME 值轉換為 VARCHAR 的時候非常方便。你可以將日期轉換為一個更具可讀性的格式。我們將在下面的部分更多采取這種方式。

現在你只是看到了我們如何使用 CAST 將值從一種資料型別轉換為另一種的。下面讓我們來進一步的探討。

CAST

CAST 函式被用來將值從一種資料型別的轉換為另一種的。其是一個遵守 ANSI SQL-92 標準的函式。

該函式的語法是

CAST(value as datatype)

其中 value 是要轉換的項，datatype 是你想要將 value 轉換為的型別。

上文中的示例 CAST(E.BirthDate as VARCHAR) 將 DATETIME 型別的欄位 BIrthDate 轉換為了一個 VARCHAR 文字值。

當從一種資料型別的值轉換為一個 VARCHAR 時我通常不使用 CAST，因為我通常需要對值進行格式化；然而，我在只需要進行轉換時才使用 CAST。這種情況下我想要：

• 將一個 VARCHAR 或其他文字值轉換為一個可以進行計算的數字或 DATETIME 值，
• 需要將數值轉換為同一種型別，比如當 INT 與 FLOAT 結合使用時。

思考一下這個例子。產品經理想要將手頭上的庫存數量減少 10%。新的總額為多少？

我們開始使用以下查詢語句

SELECT   I.ProductID,
         I.LocationID,
         I.Shelf,
         I.Bin,
         I.Quantity,
         I.Quantity * .90 as ReducedQuantity
FROM     Production.ProductInventory I
ORDER BY I.ProductID, I.LocationID

你將以下結果遞交給產品經理

帶有小數位的減少後的數量 （上圖的說明）

但請注意 ReducedQuantity 列。帶有小數位！產品經理會抱怨這一點的。顯而易見，她會使用憤怒的語氣來嘲諷你，我們的庫存中不可能有半輛自行車，那麼為什麼報表種會這樣顯式？

該怎麼辦呢？使用 CAST 來將計算的值轉換回一個整數。

我們可以使用下面的查詢語句來做到這點

SELECT   I.ProductID,
         I.LocationID,
         I.Shelf,
         I.Bin,
         I.Quantity,
         CAST(I.Quantity * .90 as SMALLINT) as ReducedQuantity
FROM     Production.ProductInventory IORDER BY I.ProductID, I.LocationID

你將下列結果傳送給認為他們可以接受的管理者：

減少數量後的結果集 – 消除了小數位

在這個例子中，我使用將 quantity 轉換為 SMALLINT 的方式將這一列的資料型別變成了 SMALLINT，我也可以將其轉換為 INT，不過我想要保持型別一致。

CONVERT

CONVERT 函式被用來將一個值從一種資料型別轉換為另一種，並可以指定其格式。其是 SQL Server 特有的，而不是一個遵守 ANSI SQL-92 的函式。

該函式的語法如下

CONVERT(datatype, value, style)

說明

• datatype 是你想要將 value 轉換為的型別
• value 是你想要轉換的項
• style 是你想要得到的轉換後的值的格式。

CONVERT 函式真正的亮點在於你想要顯式日期或數值的文字時。我們來說一下日期的轉換。只要你學會了其訣竅，那麼數值的轉換也不在話下。

轉換 DATETIME 型別

思考下列語句：

SELECT 'Today''s date is ' + CAST(GETDATE() as varchar)

返回了結果

Today’s date is Jul  4 2015 10:35AM

有三件事情需要注意：

1. 我使用兩個單引號來表示文字值內的單引號。這就是所謂的“逃逸”字元（參看 Constants, Transact-SQL）。 我們必須這樣做來使 SQL 不認為單引號是文字值的結尾。
2. GETDATE() 函式用於返回當前日期。
3. 返回的日期並不是適合閱讀的最佳格式。

那麼我們如何來修正這個格式呢？我們可以使用 CONVERT 並指定一個樣式。

這樣

SELECT 'Today''s date is ' + CONVERT(VARCHAR, GETDATE(), 101)

將返回以下結果

Today’s date is 07/04/2015

好多了！

CONVERT 語句種的 “101” 就是樣式。MSDN 上有更多樣式的列表，但是在轉換日期時有下列一些最流行的格式：

轉換函式 DATETIME 的格式

提示:  你會注意到有兩種格式存在。顯示日期時你可以選擇顯示或不顯示世紀。單數字格式只顯示兩位數的年（yy）；用四們數字世紀（yyyy)。

記住：格式是轉化的可選項。

總結

CAST 和 CONVERT 都是用來將一種型別的數值轉換成另一種型別。CAST 遵循 ANSI SQL-92 標準，所以如果你需要在不同的資料庫執行，如 Oracle，Mysql，SQL Server，你寫 sql 時最好使用 CAST。

CONVERT 不遵循 ANSI SQL-92 標準。它的好處是你在轉換數值時可以指定格式。這很有用，特別是你處理日期數值，將數值轉換成 text。

需要遵循 SQL-92 寫程式碼時請使用 CAST，需要更多的格式請使用 .CONVERT。