[MS SQL]SQL表达式CAST和CONVERT讲解

MSDN：http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

1.CAST和CONVERT用法简介

CAST的用法比较简单：

 CAST（表达式 AS 数据类型）

如：

 cast(isnull(column1, 0) as money)

 cast(isnull(column2, 0) as int)

 CAST('abc' AS varchar(5))

 declare @iInvDate datetime

 CAST(@iInvDate AS NVARCHAR(30))

CONVERT用法如下：

 convert(float, '1.23')

2. 嵌套CAST用法

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))

最好不要尝试将decimal或者numeric类型转换成binary类型。

下面给出的例子显示了如何使用CAST和CONVERT。

 USE pubs

 GO 

 SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales

 FROM titles

 WHERE CAST(ytd_sales AS char(20)) LIKE '3%' 

 GO

用CONVERT.

 USE pubs

 GO 

 SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales

 FROM titles

 WHERE CONVERT(char(20), ytd_sales) LIKE '3%' 

 GO

这两句执行结果是一样的。

3.在数学计算中用CAST函数：

 USE pubs

 GO 

 SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies' 

 FROM titles

 GO

4. 用CAST来连接字符串

 USE pubs

 GO 

 SELECT 'The price is ' + CAST(price AS varchar(12))

 FROM titles

 WHERE price > 10.00

 GO

显示结果如下：

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

The price is 19.99 The price is 11.95 The price is 19.99 The price is 19.99  

5.用CAST来截取合适长度的字符串

 USE pubs

 GO 

 SELECT CAST(title AS char(50)), ytd_sales

 FROM titles

 WHERE type = 'trad_cook' 

 GO

6.在LIKE里用CAST函数

 USE pubs

 GO 

 SELECT title, ytd_sales

 FROM titles

 WHERE CAST(ytd_sales AS char(20)) LIKE '15%' 

 AND type = 'trad_cook' 

 GO

这个语句讲int的ytd_sales转换成字符型以便能用LIKE来进行比较。

下面是一些使用例子，以供我们自己写SQL语句时参考，不用东找西找的。

Converting Character Data

http://msdn.microsoft.com/en-us/library/aa224010(v=SQL.80).aspx

This example converts the current date to style 3, dd/mm/yyyy

 SELECT CONVERT(char(12), GETDATE(), 3)

This example converts a uniqueidentifier value to a char data type.

 DECLARE @myid uniqueidentifier

 SET @myid = NEWID()

 SELECT CONVERT(char(255), @myid) AS 'Char' 

 GO

This example shows how to convert data for display.

 USE pubs

 GO 

 SELECT title,

 CAST(ytd_sales AS CHAR(12)),

 CAST(GETDATE() AS CHAR(12))

 FROM titles

 WHERE CAST(ytd_sales AS CHAR(20) ) LIKE '1%' 

 GO

Converting binary and varbinary Data

http://msdn.microsoft.com/en-us/library/aa223991(v=SQL.80).aspx

You can convert int, smallint and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, it will be different from the original integer value if truncation has occurred.

 SELECT CAST( 123456 AS BINARY(4) )

This batch shows that the silent truncation can affect arithmetic operations without raising an error:

 DECLARE @BinaryVariable2 BINARY(2)

 SET @BinaryVariable2 = 123456

 SET @BinaryVariable2 = @BinaryVariable2 + 1

 SELECT CAST( @BinaryVariable2 AS INT)

 GO

The final result is 57921, not 123457.

Converting datetime and smalldatetime Data

http://msdn.microsoft.com/en-us/library/aa223908(v=SQL.80).aspx

The example converts smalldatetime and datetime values to varchar and binary data types, respectively.

 DECLARE @mydate_sm SMALLDATETIME

 SET @mydate_sm = '4/05/98' 

 SELECT CAST(@mydate_sm AS VARCHAR) AS SM_DATE_VARCHAR

 GO 

 DECLARE @mydate DATETIME

 SET @mydate = '4/05/98' 

 SELECT CAST(@mydate AS BINARY) AS DATE_BINARY

 GO

Here is the result set.

(1 row(s) affected) SM_DATE_VARCHAR ------------------------------ Apr 5 1998 12:00AM (1 row(s) affected) DATE_BINARY -------------------------------------------------------------- 0x0000000000000000000000000000000000000000000000008c3000000000 (1 row(s) affected) 

Converting money Data

http://msdn.microsoft.com/en-us/library/aa224003(v=SQL.80).aspx 

This example converts smallmoney and money values to varchar and decimal data types, respectively.

 USE pubs

 GO 

 DECLARE @mymoney_sm SMALLMONEY

 SET @mymoney_sm = 3148.29

 SELECT CAST(@mymoney_sm AS VARCHAR) AS "SM_MONEY VARCHAR"

 GO 

 DECLARE @mymoney MONEY

 SET @mymoney = 3148.29

 SELECT CAST(@mymoney AS DECIMAL) AS "MONEY DECIMAL"

 GO

Here is the result set

SM_MONEY VARCHAR ------------------------------ 3148.29 (1 row(s) affected) MONEY DECIMAL ---------------------- 3148 (1 row(s) affected)