CAST 和 CONVERT 将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。语法 使用 CAST:CAST ( expression AS data_type ) 使用 CONVERT:CONVERT (data_type[(length)], expression [, style])示例 A. 同时使用 CAST 和 CONVERT 每个示例都将检索书名(这些图书的截止当前销售额的第一位数字为 3),并将这些图书的 ytd_sales 转换为 char(20)。-- Use CAST. USE pubs GO SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%' GO-- Use CONVERT. USE pubs GO SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CONVERT(char(20), ytd_sales) LIKE '3%' GO下面是任一查询的结果集:Title ytd_sales ------------------------------ ----------- 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 下面的示例通过将总的截止当前销售额 (ytd_sales) 与每本图书的价格 (price) 相除,进行单独列计算 (Copies)。在四舍五入到最接近的整数后,此结果将转换为 int 数据类型。 USE pubs GO SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies' FROM titles GO下面是结果集:Copies ------ 205 324 6262 205 102 7440 NULL 383 205 NULL 17 187 16 204 418 18 1263 273 (18 row(s) affected)C. 使用 CAST 进行串联 下面的示例使用 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 The price is 22.95 The price is 20.00 The price is 21.59 The price is 10.95 The price is 19.99 The price is 20.95 The price is 11.95 The price is 14.99 (12 row(s) affected)D. 使用 CAST 获得更多易读文本 下面的示例在选择列表中使用 CAST 将 title 列转换为 char(50) 列,这样结果将更加易读。USE pubs GO SELECT CAST(title AS char(50)), ytd_sales FROM titles WHERE type = 'trad_cook' GO下面是结果集: ytd_sales -------------------------------------------------- --------- Onions, Leeks, and Garlic: Cooking Secrets of the 375 Fifty Years in Buckingham Palace Kitchens 15096 Sushi, Anyone? 4095(3 row(s) affected)E. 使用带有 LIKE 子句的 CAST 下面的示例将 int 列(ytd_sales 列)转换为 char(20) 列,以便使用 LIKE 子句。USE pubs GO SELECT title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '15%' AND type = 'trad_cook' GO下面是结果集:title ytd_sales ------------------------------------------------------------ ----------- Fifty Years in Buckingham Palace Kitchens 15096 (1 row(s) affected)
USE [JxtMsg] GO /****** 对象: StoredProcedure [dbo].[Pad_SearchMessage] 脚本日期: 05/09/2008 09:19:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 2008-04-10 -- Description: -- ============================================= ALTER PROCEDURE [dbo].[Pad_SearchMessage] @mobile varchar(12), @path varchar(50), @Ret int output AS set @Ret=1 BEGIN if (@path='72' or @path='35' or @path='22' or @path='25' or @path='41' or @path='42') begin
select fdestaddr,FUD,FDeliverTime,statusInfo from [172.16.1.213].newcoverm.dbo.billingtable b,jxt_returnstatus a where fdestaddr=@mobile and b.fstatus=cast(a.status as int)order by FDeliverTime desc if @@error=0 begin set @Ret=0 return end end else if (@path='31' or @path='34') begin select fdestaddr,FUD,FDeliverTime,statusInfo from [172.16.1.217].newcoverm.dbo.billingtable b,jxt_returnstatus a where fdestaddr=@mobile and b.fstatus=cast(a.status as int) order by FDeliverTime desc if @@error=0 begin set @Ret=0 return end end else select mobile,Msgcontent,submittime,PathName, statusInfo from jxtsms.dbo.Sms_WaitSendLog b,Msg_Path a,jxt_returnstatus c where mobile=@mobile and b.path=a.path and b.status=cast(c.status as int) order by submittime desc if @@error=0 begin set @Ret=0 return end END 我在调用这个存储过程的时候,他会抱错 说status 转换为int类型时出错这是怎么回事啊 ,执行这个存储时没有报错怎么调用的时候就报错了,是不是不能将varchar类型转换为int类型阿
cast(a.status as int) order by FDeliverTime desc 他会抱错 说status 转换为int类型时出错这是怎么回事啊---------------多半是你status有不为int型数据的字符串.如:status = '1' ,转换没问题. 如:status = 'A' ,转换出错.自己查查你status的所有值.
在where后加个 ISNUMERIC(status) = 1例如:select fdestaddr,FUD,FDeliverTime,statusInfo from [172.16.1.217].newcoverm.dbo.billingtable b,jxt_returnstatus a where ISNUMERIC(status) = 1 and fdestaddr=@mobile and b.fstatus=cast(a.status as int) order by FDeliverTime desc
将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。语法
使用 CAST:CAST ( expression AS data_type ) 使用 CONVERT:CONVERT (data_type[(length)], expression [, style])示例
A. 同时使用 CAST 和 CONVERT
每个示例都将检索书名(这些图书的截止当前销售额的第一位数字为 3),并将这些图书的 ytd_sales 转换为 char(20)。-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO下面是任一查询的结果集:Title ytd_sales
------------------------------ -----------
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
下面的示例通过将总的截止当前销售额 (ytd_sales) 与每本图书的价格 (price) 相除,进行单独列计算 (Copies)。在四舍五入到最接近的整数后,此结果将转换为 int 数据类型。 USE pubs
GO
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles
GO下面是结果集:Copies
------
205
324
6262
205
102
7440
NULL
383
205
NULL
17
187
16
204
418
18
1263
273 (18 row(s) affected)C. 使用 CAST 进行串联
下面的示例使用 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
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99 (12 row(s) affected)D. 使用 CAST 获得更多易读文本
下面的示例在选择列表中使用 CAST 将 title 列转换为 char(50) 列,这样结果将更加易读。USE pubs
GO
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
GO下面是结果集: ytd_sales
-------------------------------------------------- ---------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095(3 row(s) affected)E. 使用带有 LIKE 子句的 CAST
下面的示例将 int 列(ytd_sales 列)转换为 char(20) 列,以便使用 LIKE 子句。USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
GO下面是结果集:title ytd_sales
------------------------------------------------------------ -----------
Fifty Years in Buckingham Palace Kitchens 15096 (1 row(s) affected)
GO
/****** 对象: StoredProcedure [dbo].[Pad_SearchMessage] 脚本日期: 05/09/2008 09:19:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: -- Create date: 2008-04-10
-- Description: -- =============================================
ALTER PROCEDURE [dbo].[Pad_SearchMessage]
@mobile varchar(12),
@path varchar(50),
@Ret int output
AS
set @Ret=1
BEGIN
if (@path='72' or @path='35' or @path='22' or @path='25' or @path='41' or @path='42')
begin
select fdestaddr,FUD,FDeliverTime,statusInfo from [172.16.1.213].newcoverm.dbo.billingtable b,jxt_returnstatus a where fdestaddr=@mobile and b.fstatus=cast(a.status as int)order by FDeliverTime desc
if @@error=0
begin
set @Ret=0
return
end
end
else
if (@path='31' or @path='34')
begin
select fdestaddr,FUD,FDeliverTime,statusInfo from [172.16.1.217].newcoverm.dbo.billingtable b,jxt_returnstatus a where fdestaddr=@mobile and b.fstatus=cast(a.status as int) order by FDeliverTime desc
if @@error=0
begin
set @Ret=0
return
end
end
else
select mobile,Msgcontent,submittime,PathName, statusInfo from jxtsms.dbo.Sms_WaitSendLog b,Msg_Path a,jxt_returnstatus c where mobile=@mobile and b.path=a.path and b.status=cast(c.status as int) order by submittime desc
if @@error=0
begin
set @Ret=0
return
end
END
我在调用这个存储过程的时候,他会抱错 说status 转换为int类型时出错这是怎么回事啊 ,执行这个存储时没有报错怎么调用的时候就报错了,是不是不能将varchar类型转换为int类型阿
如:status = 'A' ,转换出错.自己查查你status的所有值.
from [172.16.1.217].newcoverm.dbo.billingtable b,jxt_returnstatus a
where ISNUMERIC(status) = 1 and fdestaddr=@mobile and b.fstatus=cast(a.status as int)
order by FDeliverTime desc
这样能不能转换
select convert (int, 'a')
select cast( 'a' as int)。服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 'a' 转换为数据类型为 int 的列时发生语法错误
如果可以转换的话,会转换成功,否则会抱错的,使用前最好使用isnumberic()判断一下