[转]SQL Server 中 Cast 与 Convert

两者都用于：将一种数据类型的表达式转换为另一种数据类型的表达式。

安装有 Sql Server 2008 时可以浏览：ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm

语法

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

参数

expression

任何有效的表达式。

data_type

目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。有关可用数据类型的详细信息，请参阅数据类型 (Transact-SQL)。

length

指定目标数据类型长度的可选整数。默认值为 30。

style

指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL，则返回 NULL。该范围是由 data_type 确定的。有关详细信息，请参阅“备注”部分。

返回类型

返回转换为 data_type 的 expression。

注释

Date 和 Time 样式

如果 expression 为 date 或 time 数据类型，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

 

生成 XML 实例。

1

保留无用空格。此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space="preserve" 的行为相同。

2

启用有限的内部 DTD 子集处理。

如果启用，则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。

• 应用属性的默认值。
  
  
• 解析并扩展内部实体引用。
  
  
• 检查 DTD 内容模型以实现语法的正确性。
  
  

分析器将忽略外部 DTD 子集。此外，不评估 XML 声明来查看 standalone 属性是设置为 yes 还是 no，而是将 XML 实例当成一个独立文档进行分析。

3

保留无用空格，并启用有限的内部 DTD 子集处理。

二进制样式

如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n)，则 style 可以为下表中显示的值之一。表中没有列出的样式值将返回错误。

 

对用户定义类型执行操作。有关如何从 xml 数据类型进行转换的详细信息，请参阅生成 XML 实例。

xml 数据类型

当您将 xml 数据类型显式或隐式转换为字符串或二进制数据类型时，xml 数据类型的内容将根据一组规则进行序列化。有关这些规则的信息，请参阅 XML 数据的序列化。有关如何从 XML 转换为 CLR 用户定义类型的信息，请参阅对用户定义类型执行操作。有关如何从其他数据类型转换到 xml 数据类型的信息，请参阅生成 XML 实例。

文本和图像数据类型

不支持对 text 和 image 数据类型进行自动数据类型转换。可将 text 数据显式转换为字符数据，将 image 数据转换为 binary 或 varbinary，但最大长度是 8000 字节。如果试图进行不正确的转换，如将包含字母的字符表达式转换为 int，则 SQL Server 将返回错误消息。

输出排序规则

如果 CAST 或 CONVERT 的输出是字符串，并且输入也是字符串，则输出将与输入具有相同的排序规则和排序规则标签。如果输入不是字符串，则输出采用数据库的默认排序规则以及强制默认的排序规则标签。有关详细信息，请参阅排序规则优先级 (Transact-SQL)。

若要为输出分配不同的排序规则，请将 COLLATE 子句应用于 CAST 或 CONVERT 函数的结果表达式。例如：

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

截断结果和舍入结果

将字符或二进制表达式（char、nchar、nvarchar、varchar、binary 或 varbinary）转换为其他数据类型的表达式时，可截断数据，仅显示部分数据，或返回错误（因为结果太短而无法显示）。除了下表显示的转换，其他到 char、varchar、nchar、nvarchar、binary 和 varbinary 的转换都将被截断。

 

被转换的数据类型	转换为的数据类型	结果
int、smallint 或 tinyint	char	*
	varchar	*
	nchar	E
	nvarchar	E
money、smallmoney、numeric、decimal、float 或 real	char	E
	varchar	E
	nchar	E
	nvarchar	E

* = 结果长度太短而无法显示。E = 因为结果长度太短无法显示而返回错误。

SQL Server 仅保证往返转换（即从原始数据类型进行转换后又返回原始数据类型的转换）在各版本间产生相同值。以下示例显示的即是这样的往返转换：

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

注意：

不要尝试构造 binary 值然后将其转换为数值数据类型类别的一种数据类型。SQL Server 不能保证 decimal 或 numeric 数据类型到 binary 的转换结果在 SQL Server 的各个版本中都相同。

以下示例显示了由于太小而无法显示的结果表达式。

	复制代码
USE AdventureWorks2008R2; GO SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave' FROM HumanResources.Employee e JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID WHERE NOT e.BusinessEntityID >5;

下面是结果集：

FirstName LastName Title Sick Leave

--------- --------- ------------------- -----------

Ken Sanchez NULL *

Terri Duffy NULL *

Roberto Tamburello NULL *

Rob Walters NULL *

Gail Erickson Ms. *

(5 row(s) affected)

转换小数位数不同的数据类型时，结果值有时被截断，有时被舍入。下表显示了此行为。

 

被转换的数据类型	转换到的数据类型	行为
numeric	numeric	舍入
numeric	int	截断
numeric	money	舍入
money	int	舍入
money	numeric	舍入
float	int	截断
float	numeric	舍入
float	datetime	舍入
datetime	int	舍入

例如，以下转换的结果为 10：

SELECT CAST(10.6496 AS int)

在进行数据类型转换时，若目标数据类型的小数位数小于源数据类型的小数位数，则该值将被截断。例如，以下转换的结果为 $10.3497：

SELECT CAST(10.3496847 AS money)

当非数字型 char、nchar、varchar 或 nvarchar 数据转换为 int、float、numeric 或 decimal 时，SQL Server 将返回错误消息。当空字符串 (" ") 转换为 numeric 或 decimal 时，SQL Server 也返回错误。

在 SQL Server 2005 及更高版本中，某些 datetime 转换具有不确定性

在 SQL Server 2000 中，从 string 到 date 和 time 的转换被标记为确定性转换。但是，这对于下表中列出的样式却不成立。对于这些样式，相应的转换取决于语言设置。SQL Server 2005 及更高版本将这些转换标记为不确定性转换。

下表列出了从 string 到 datetime 的转换为不确定性转换的样式。

 

低于 100 的所有样式1	106
107	109
113	130

¹ 样式 20 和 21 例外

示例

A. 同时使用 CAST 和 CONVERT

每个示例都检索列表价格的第一位是 3 的产品的名称，并将 ListPrice 转换为 int。

	复制代码
-- Use CAST USE AdventureWorks2008R2; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE '3%'; GO -- Use CONVERT. USE AdventureWorks2008R2; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CONVERT(int, ListPrice) LIKE '3%'; GO

B. 使用包含算术运算符的 CAST

以下示例将本年度截止到现在的全部销售额 (SalesYTD) 除以佣金百分比 (CommissionPCT)，从而得出单列计算结果 (Computed)。在舍入到最接近的整数后，将此结果转换为 int 数据类型。

	复制代码
USE AdventureWorks2008R2; GO SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed' FROM Sales.SalesPerson WHERE CommissionPCT != 0; GO

下面是结果集：

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

C. 使用 CAST 进行连接

以下示例使用 CAST 连接非字符型非二进制表达式。

	复制代码
USE AdventureWorks2008R2; GO SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice FROM Production.Product WHERE ListPrice BETWEEN 350.00 AND 400.00; GO

下面是结果集：

ListPrice

------------------

The list price is 357.06

The list price is 364.09

The list price is 364.09

The list price is 364.09

The list price is 364.09

(5 row(s) affected)

D. 使用 CAST 生成可读性更高的文本

以下示例使用选择列表中的 CAST 将 Name 列转换为 char(10) 列。

	复制代码
USE AdventureWorks2008R2; GO SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID WHERE Name LIKE 'Long-Sleeve Logo Jersey, M'; GO

下面是结果集：

Name UnitPrice

---------- ---------------------

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

E. 使用包含 LIKE 子句的 CAST

下面的示例将 money 列 SalesYTD 转换为 int 列，然后再转换为 char(20) 列，以便可以将其用于 LIKE 子句。

	复制代码
USE AdventureWorks2008R2; GO SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID FROM Person.Person p JOIN Sales.SalesPerson s ON p.BusinessEntityID = s.BusinessEntityID WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%'; GO

下面是结果集：

FirstName LastName SalesYTD SalesPersonID

---------------- ------------------- ---------------- -------------

Tsvi Reiter 2811012.7151 279

Syed Abbas 219088.8836 288

Rachel Valdez 2241204.0424 289

(3 row(s) affected)

F. 使用包含类型化的 XML 的 CONVERT 或 CAST

下面的几个示例显示如何通过 xml 数据类型使用 CONVERT 转换为类型化的 XML。

此示例将包含空格、文本和标记的字符串转换为类型化的 XML，并删除所有无用空格（节点之间的边界空格）:

	复制代码
CONVERT(XML, '<root><child/></root>')

此示例将包含空格、文本和标记的类似字符串转换为类型化的 XML，并保留无用空格（节点之间的边界空格）：

	复制代码
CONVERT(XML, '<root> <child/> </root>', 1)

此示例将包含空格、文本和标记的字符串转换为类型化的 XML：

	复制代码
CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)

有关详细信息，请参阅生成 XML 实例。

G. 对日期时间数据使用 CAST 和 CONVERT

以下示例显示了当前日期和时间，并使用 CAST 将当前日期和时间改为字符数据类型，然后使用 CONVERT 以 ISO 8901 格式显示日期和时间。

	复制代码
SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS nvarchar(30)) AS UsingCast, CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ; GO

下面是结果集：

UnconvertedDateTime UsingCast UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM 2006-04-18T09:58:04.570

(1 row(s) affected)

以下示例大致与上述示例相反。该示例将日期和时间显示为字符数据，并使用 CAST 将字符数据改为 datetime 数据类型，然后使用 CONVERT 将字符数据改为 datetime 数据类型。

	复制代码
SELECT '2006-04-25T15:50:59.997' AS UnconvertedText, CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast, CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ; GO

下面是结果集：

UnconvertedText UsingCast UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997

(1 row(s) affected)

H. 对二进制和字符数据使用 CONVERT

以下示例显示了使用不同样式转换二进制和字符数据的结果。

	复制代码
--Convert the binary value 0x4E616d65 to a character value. SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'

下面是结果集：

Style 0, binary to character

----------------------------

Name

(1 row(s) affected)

	复制代码
--The following example shows how Style 1 can force the result --to be truncated. The truncation is caused by --including the characters 0x in the result. SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'

下面是结果集：

Style 1, binary to character

------------------------------

0x4E616D

(1 row(s) affected)

	复制代码
--The following example shows that Style 2 does not truncate the --result because the characters 0x are not included in --the result. SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'

下面是结果集：

Style 2, binary to character

------------------------------

4E616D65

(1 row(s) affected)

	复制代码
--Convert the character value 'Name' to a binary value. SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, character to binary'

下面是结果集：

Style 0, character to binary

----------------------------------

0x4E616D6500000000

(1 row(s) affected)

	复制代码
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'

下面是结果集：

Style 1, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)

	复制代码
SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style 2, character to binary'

下面是结果集：

Style 2, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)

请参阅

参考

SELECT (Transact-SQL)
系统函数 (Transact-SQL)

其他资源

数据类型转换（数据库引擎）
编写国际化 Transact-SQL 语句

帮助和信息

获取 SQL Server 2008 帮助

© 2010 Microsoft Corporation。保留所有权利。