sql cast和convert用法詳解

sql cast與convert用法詳解

 

總結：

以下這只適用於mysql

SELECT {fn CONCAT(CONVERT(user_id,CHAR),USER_NAME)} str 是 FROM_CAT(CONVERT(user_id,CHAR),USER_NAME)} struser於sqlserver2008

SELECT {fn CONCAT(CONVERT(CHAR，user_id),USER_NAME)} AS str FROM t_sys_user

以下這條sqlserver和mysql相容

SELECT {fn CONCAT(CAST),_RidER str FROM t_sys_user

 

註解：cast是型別轉換函數，sqlserver和mysql通用。

            convert也是型別轉換，在sqlserver2008和mysql中都存在，但是參數順序相反。

            concat函數在sqlserver2008和mysql中均有，但是在sqlserver2008中需要這樣適用{fn concat(arg0,arg1,.....)}且不適用與int類型和varchar類型連接。 sqlserver2008中字串連線一般用「+」號。不過在sqlserver2012中好像支援直接使用concat函數。

 

mysql可以轉換成的型別限制：

就是CAST(xxx AS 型), CONVERT(xxx,型)。

可以轉換的型別是有限制的。這個型別可以是以下值其中的一個：

二進制，同帶binary前綴的效果: BINARY    

字符型，可帶參數: CHAR()     

化日期: DATE DATETIME     

浮點數: DECIMAL      

整數: SIGNED     

無符號整數: UNSIGNED  下列部份摘要為上述部分摘要為上述部分。

 

sqlserver

將某種資料類型的表達式明確轉換為另一種資料型別。 CAST 和 CONVERT 提供相似的功能。

語法

使用CAST：

CAST ( expression AS data_type )

使用CONVERT：

CONVERT (data_type[(length)], expression [身） Microsoft® SQL Server™ 表達式。有關更多信息，請參見表達式。

data_type

目標系統所提供的資料類型，包括 bigint 和 sql_variant。不能使用使用者定義的資料類型。有關可用的數據類型的更多信息，請參見數據類型。

length

nchar、nvarchar、char、varchar、binary 或 varbinary 資料類型的可選參數。

style

日期格式樣式，藉以將datetime 或smalldatetime 資料轉換為字元資料（nchar、nvarchar、char、varchar、nchar 或nvarchar 資料型別）；或字串格式樣式，藉以將float、real、money 或smallmoneyoneyoney資料轉換為字元資料（nchar、nvarchar、char、varchar、nchar 或nvarchar 資料類型）。

SQL Server 支援使用科威特演算法的阿拉伯樣式中的資料格式。

在表中，左側的兩個欄位表示將 datetime 或 smalldatetime 轉換為字元資料的 style 值。給 style 值加 100，可獲得包含世紀數位的四位年份 (yyyy)。

不帶世紀數字(yy) 帶世紀數位(yyyy) 

標準 

輸入/輸出** 

- 0 或100 (*)  美國mm/dd/yyyy 

2 102 ANSI yy.mm.dd 

3 103 英國/法國dd/mm/yy 

4 104 德國dd.mm.yy 

6 106 - dd mon yy 

7 107 - mon dd, yy 

8 108 - hh:mm:ss 

- 9 或109 (*) ” PM） 

10 110 美國mm-dd-yy 

11 111 日本yy/mm/dd 

12 112 ISO yymmdd 

:mmm(24h) 

14 114 - hh:mi:ss:mmm(24h) 

- 20 或120 (*)  ODBC 規範yyyy-mm-dd hh:mm:ss[.fff] 121 (*)  ODBC 規範（帶毫秒） yyyy-mm-dd hh:mm:ss[.fff] 

- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格） 

- 130* 科威特dd mon yyyy hh:mi:ss:mmmAM 

- 131* 科威特

  預設值（style 0 或100、9 或109、13 或隨時113、201121120112112201212201212201212201221122013 或始終）返回世紀數位(yyyy)。

** 轉換為 datetime 時輸入；轉換為字元資料時輸出。

*** 專門用於 XML。對於從 datetime 或 smalldatetime 到 character 資料的轉換，輸出格式如表所示。對於從 float、money 或 smallmoney 到 character 資料的轉換，輸出等同於 style 2。對於從 real 到 character 資料的轉換，輸出等同於 style 1。

 

重要  預設情況下，SQL Server 根據截止年份 2049 解釋兩位數字的年份。即，兩位數字的年份 49 被解釋為 2049，而兩位數字的年份 50 被解釋為 1950。許多客戶端應用程式（例如基於 OLE 自動化物件的客戶端應用程式）都使用 2030 作為截止年份。 SQL Server 提供一個設定選項（"兩位數字的截止年份"），藉以變更 SQL Server 所使用的截止年份並對日期進行一致性處理。然而最安全的辦法是指定四位數字年份。

 

當從 smalldatetime 轉換為字元資料時，包含秒或毫秒的樣式將在這些位置上顯示零。當從 datetime 或 smalldatetime 值轉換時，可以透過使用適當的 char 或 varchar 資料類型長度來截斷不需要的日期部分。

下表顯示了從 float 或 real 轉換為字元資料時的 style 值。

值 輸出 

0（預設值） 最大為 6 位數。根據需要使用科學記數法。 

1 始終為 8 位元值。始終使用科學記數法。 

2 始終為 16 位元值。始終使用科學記數法。

 

在下表中，左列表示從 money 或 smallmoney 轉換為字元資料時的 style 值。

值 輸出 

0（預設值） 小數點左側每三位數字之間不以逗號分隔，小數點右側取兩位數，例如 4235.98。 

1 小數點左側每三位數字之間以逗號分隔，小數點右邊取兩位數，例如 3,510.92。 

2 小數點左側每三位數字之間不以逗號分隔，小數點右側取四位數，例如 4235.9819。 

 

回傳類型

傳回與 data type 0 相同的值。

註解

隱性轉換指那些沒有指定 CAST 或 CONVERT 函數的轉換。而明確轉換指那些已指定了所需 CAST (CONVERT) 函數的轉換。下面的圖表顯示了所有可用於 SQL Server 系統提供的資料類型的明確和隱性轉換，這些資料類型包括 bigint 和 sql_variant。

 

 

說明  因為 Unicode 資料總是使用偶數位元組，所以當在 binary 或 varbinary 資料類型與 Unicode 支援的資料類型之間進行轉換時會使用提示。例如，此轉換不會傳回41 的十六進位值，而是傳回4100 的十六進位值：SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)

 

不支援text 和image 資料類型的自動資料型別轉換。可以將 text 數據明確轉換為字元數據，將 image 數據顯性轉換為 binary 或 varbinary 數據，但是最大長度為 8000。如果嘗試進行不正確的轉換（例如，將包含字母的字元表達式轉換為 int），則 SQL Server 會產生錯誤訊息。

當 CAST 或 CONVERT 的輸出是字串且輸入也是字串時，輸出與輸入具有相同的排序規則和排序規則標籤。如果輸入不是字串，則輸出採用資料庫的預設排序規則及強制預設的排序規則標籤。有關更多信息，請參見排序規則的優先順序。

若要指派輸出不同的排序規則，請將 COLLATE 子句套用到 CAST 或 CONVERT 函數的結果表達式中。例如：

SELECT CAST(''abc'' AS varchar(5)) COLLATE French_CS_AS

不存在有關賦值的從 sql_variant 資料類型進行的隱性轉換，但存在轉換為 sql_variant 的隱性轉換。

將字元或二進位表達式（char、nchar、nvarchar、varchar、binary 或varbinary）轉換為不同資料類型的表達式時，資料可能會被截斷，只顯示一部分，或者因為結果太短無法顯示而返回錯誤。除下表所示的轉換外，轉換為 char、varchar、nchar、nvarchar、binary 和 varbinary 時將被截斷。

 varchar * 

  nchar E 

  nvarchar E 

money、smallmoney、numeric、decimal、float 或real char E 

varchar E

 

* 結果長度太短而無法顯示。

E 因為結果長度太短無法顯示而回傳錯誤。

Microsoft SQL Server 僅保證往返轉換（即，從原始資料型別轉換後再傳回原始資料型別）在各版本間產生相同值。以下的範例顯示往返轉換：

DECLARE @myval decimal (5, 2)SET @myval = 193.57SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))-- Or, using CONVERTSELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

例如，不要嘗試建構binary 值並將它們轉換為數字資料類型分類的資料類型。 SQL Server 並不保證 decimal 或 numeric 資料型別轉換為 binary 的結果在 SQL Server 各版本間相同。

下面的範例顯示了由於太短而無法顯示的結果表達式。

USE pubsSELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))FROM titlesWHERE type = ''trad_cook''

  -------- ----------------- -- Onions, Leeks, and Garlic *  Fifty Years in Buckingham *  Sushi, Anyone?            *  (3 row(s) affected)

當具有不同小數位數的資料類型進行轉換時，值將被截斷為最精確的數字。例如，SELECT CAST(10.6496 AS int) 的結果為 10。

轉換時，若目標資料型別的小數位數小於來源資料型別的小數位數，則要轉換的值將會四捨五入。例如，CAST(10.3496847 AS money) 的結果是 $10.3497。

當將非數字類型的 char、nchar、varchar 或 nvarchar 資料轉換為 int、float、numeric 或 decimal 時，SQL Server 將傳回錯誤訊息。當將空字串 (" ") 轉換為 numeric 或 decimal 時，SQL Server 也會傳回錯誤訊息。

使用二進位字串資料

當 binary 或 varbinary 資料轉換為字元資料並且在 x 後面指定了奇數位的值時，SQL Server 在 x 後面添加 0（零）以成為偶數字值。

二進位資料包含從 0 到 9 和從 A 到 F（或從 a 到 f）的字符，每兩個字符為一組。二進位字串必須以 0x 開頭。例如，若要輸入 FF，請鍵入 0xFF。最大值是一個 8000 位元組的二進位值，每個位元組的最大值都是 FF。 Binary 資料型別不能用於十六進位數據，而是用於位元模式。對於儲存為二進位資料的十六進位數字的轉換和計算結果，無法保證其準確性。

當指定 binary 資料類型的長度時，每兩個字元被算作一個單位長度。長度 10 表示將輸入 10 個雙字元組。

由 0x 表示的空二進位字串可以儲存為二進位資料。

範例

A. 同時使用CAST 和CONVERT

每個範例都將檢索書名（這些圖書的截止當前銷售額的第一位數字為3），並將這些圖書的ytd_sales 轉換為char(20 )。

-- Use CAST.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE ''3%''GO-- Use CONVERT, SpuberING 1, 30) AS Title, ytd_salesFROM titlesWHERE CONVERT(char(20), ytd_sales) LIKE ''3%''GO

下面是任一查詢的結果集：

  yt h    --------- --------------------- ----------- Cooking with Computers: Surrep 3876        Computer Phobic AND Non-Phobic 375         Emotional Security: A New Algo 3336        Onions, Leeks, and Garlic: Coo 375         (4 row(s) affected)

B. 使用帶有算術運算元的CAST圖書的價格(price) 相除，進行單獨列計算(Copies)。在四捨五入到最接近的整數後，此結果將轉換為 int 資料型別。

USE pubsGOSELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS ''Copies''FROM titlesGO

下面是結果集：

Copies  62        205         102         7440        NULL       383      NULL       NULL        17          187         16          204       273         (18 row(s) affected)

C. 使用CAST 進行串聯

下面的範例使用CAST 資料型別轉換函數來串聯非字元、非二元表達式

下面的範例使用CAST 資料型別轉換函數來串聯非字元、非二進位表達式。

USE pubsGOSELECT ''The price is '' + CAST(price AS varchar(12))FROM titlesWHERE price > 10.00GO

下面是結果集：

-------------- ---- The price is 19.99        The price is 11.95        The price is 19.99        The price is 19.99.99        The price is 19.99   5 rice 20.00        The price is 21.59        The price is 10.95        The price is 19.99         The price is 14.99        (12 row(s) affected)

D. 使用CAST 取得更多易讀文字

下面的範例在選擇清單中使用 CAST 將 title 列轉換為 char(50) 列，這樣結果將會更加易讀。

USE pubsGOSELECT CAST(title AS char(50)), ytd_salesFROM titlesWHERE type = ''trad_cook''GO

下面是結果集：                          ytd_sales---------------- ----------------------------------     ---------Onions, Leeks, and Garlic: Cooking Secrets of the      375Fifty Years in Buckingham Palace Kitchens              15096Sushi, Anyone?        4095(3 row(s) affected)

E. 使用帶有LIKE 子句的CAST

下面的範例將int 欄位（ytd_sales 欄位）轉換為char (20) 列，以便使用LIKE 子句。

USE pubsGOSELECT title,

-

ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE ''15%'' AND type = ''trad_cook '' tle ytd_sales   ------------------------------------------------- -----------

 

Mysql:

 

MySQL 的CAST()和CONVERT()函數可用於取得一個類型的值，並產生另一個類型的值。兩者俱體的語法如下：

1 CAST(value as type);

2 CONVERT(value, type);

就是CAST(xxx AS 型), CONVERT(xxx,類型)。

可以轉換的型別是有限制的。這個型別可以是以下值其中的一個：

二進制，同帶binary前綴的效果: BINARY    

字符型，可帶參數: CHAR()     

化日期: DATE無題數： 3',SIGNED);

2 +- ---------------------+

3 | CONVERT('23',SIGNED) |

4 +----------- -----------+

5 |                   23 |

6 +----------------------------------+

7 1 row inset

例二

1 mysql> SELECTCAST('125e342.83'ASsigned);

2 +-------------------------- ----+

3 | CAST('125e342.83'ASsigned) |

4 +--------------------------- ---+

5 |                          125 |

6 +------------------------------+

7 1 1 1 1 row inset

例三

1 mysql> SELECTCAST('3.35'ASsigned);

2 +------------------------+

3 | CAST('3.35'ASsigned) |

4 +------------------------+

5 |                      3 |

6 +-- ----------------------+

7 1 row inset

像上面例子一樣，將varchar 轉為int 用cast(a as signed)，其中a為varchar類型的字串。

例4

在SQL Server中，下面的程式碼示範了datetime變數中，當僅包含單純的日期和單純的時間時，日期儲存的十六進位儲存表示結果。

01 DECLARE @dt datetime

02

03 --單純的日期

04 SET @dt='1900-1-2'

05 SELECT CAST(@dt asbin(結果: 0x0000000100000000

07

08 --單純的時間

09 SET @dt='00:00:01'

10 SELECT CAST(00:00:08 00012C

MySQL的型別轉換和SQL Server一樣，就是型別參數有點不同：CAST(xxx AS 類型) , CONVERT(xxx,型)。