将数据类型 varchar 转换为 bigint 时出错,无论使用convert,还是cast

海蓝树 2011-03-03 12:04:16
无论是使用,update dbo.Archives_CM set id=convert(bigint,(right(villagecode,4)+homecode+humancode))
还是
update dbo.Archives_CM set id=cast((right(villagecode,4)+homecode) as bigint)

都是报一样的错,


将数据类型 varchar 转换为 bigint 时出错。
...全文
733 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaoyehua 2011-03-03
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 dawugui 的回复:]
SQL code
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?



发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
[/Quote]
顶乌龟
--小F-- 2011-03-03
  • 打赏
  • 举报
回复
难道前4位有空字符串?
dawugui 2011-03-03
  • 打赏
  • 举报
回复
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?


发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
AcHerat 元老 2011-03-03
  • 打赏
  • 举报
回复
isnumeric(right(villagecode,4)+homecode) 都是 1 ?
海蓝树 2011-03-03
  • 打赏
  • 举报
回复
使用ISNUMERIC后发现全部都是1,但是应该如何改呢
AcHerat 元老 2011-03-03
  • 打赏
  • 举报
回复
[Quote=引用楼主 xwnxwn 的回复:]
无论是使用,update dbo.Archives_CM set id=convert(bigint,(right(villagecode,4)+homecode+humancode))
还是
update dbo.Archives_CM set id=cast((right(villagecode,4)+homecode) as bigint)

都是报一样的错,


将数据类型 ……
[/Quote]

楼主要看看是不是所有的villagecode的前四位能够转换为bigint类型!
dawugui 2011-03-03
  • 打赏
  • 举报
回复
ISNUMERIC(right(villagecode,4)+homecode) = 1

ISNUMERIC
确定表达式是否为一个有效的数字类型。

语法
ISNUMERIC ( expression )

参数
expression

要计算的表达式。

返回类型
int

注释
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。

示例
A. 使用 ISNUMERIC
下面的示例返回 1,这是因为 zip 列包含有效的数值。

USE pubs
SELECT ISNUMERIC(zip)
FROM authors
GO

B. 使用 ISNUMERIC 和 SUBSTRING
下面的示例对于 titles 表中的所有书名都返回 0,这是因为没有一个书名是有效的数值。

USE pubs
GO
-- Because the title column is all character data, expect a result of 0
-- for the ISNUMERIC function.
SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title)
FROM titles
GO

下面是结果集:

type price
--------------- -------------------------- -----------
The Busy Execut 19.99 0
Cooking with Co 11.95 0
You Can Combat 2.99 0
Straight Talk A 19.99 0
Silicon Valley 19.99 0
The Gourmet Mic 2.99 0
The Psychology (null) 0
But Is It User 22.95 0
Secrets of Sili 20.00 0
Net Etiquette (null) 0
Computer Phobic 21.59 0
Is Anger the En 10.95 0
Life Without Fe 7.00 0
Prolonged Data 19.99 0
Emotional Secur 7.99 0
Onions, Leeks, 20.95 0
Fifty Years in 11.95 0
Sushi, Anyone? 14.99 0

(18 row(s) affected)

快溜 2011-03-03
  • 打赏
  • 举报
回复
检查字段是否能转化为bigint。
王向飞 2011-03-03
  • 打赏
  • 举报
回复
select right(villagecode,4)+homecode from table

结果贴出来看看是不是有字符串????
So_CooL 2011-03-03
  • 打赏
  • 举报
回复
declare @homecode varchar(20) ='123456'
declare @humancode varchar(20) ='456789'
declare @villagecode varchar(20)
set @villagecode = 'A0000001'

declare @Archives_CM table(id int)
insert into @Archives_CM
select 1
update @Archives_CM set id =
CAST(right(@villagecode,4) as int)+
cast(@humancode as int) +
cast(@homecode as int)
select * from @Archives_CM
So_CooL 2011-03-03
  • 打赏
  • 举报
回复
update dbo.Archives_CM set id=cast(right(villagecode,4) as int)+cast(homecode as int)+ cast(humancode as int)

测试:

declare @homecode varchar(20) ='123456'
declare @humancode varchar(20) ='456789'
declare @villagecode varchar(20)
set @villagecode = 'A0000001'

declare @Archives_CM table(id int)
insert into @Archives_CM
select 1
update @Archives_CM set id =
CAST(right(@villagecode,4) as int)+
cast(@humancode as int) +
cast(@homecode as int)
select * from @Archives_CM
gogodiy 2011-03-03
  • 打赏
  • 举报
回复
update dbo.Archives_CM set id=convert(bigint,convert(bigint,(right(villagecode,4))+convert(bigint,homecode)+convert(bigint,humancode)))
实在不行这样试试,或者别用BIGING,用INT
海蓝树 2011-03-03
  • 打赏
  • 举报
回复
CREATE TABLE [Archives_CM] (
[id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[VillageCode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CMCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CMType] [smallint] NULL ,
[HomeType] [smallint] NULL ,
[HomeCode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[HumanCode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FamilyRelation] [smallint] NULL ,
[Gender] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[Nation] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Birthday] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[IDCard] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[TelePhone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Pay_Own] [decimal](15, 2) NULL ,
[Pay_Aid] [decimal](15, 2) NULL ,
[AidDept] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Bak] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Creater] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
[IfCM] [bit] NOT NULL ,
[RecordDate] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Photo] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CMYear] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[MarriageState] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[HomeHistory] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Images] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[sec_content] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[initial_money] [decimal](18, 0) NULL ,
[xmbma] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[password] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_Archives_CM] PRIMARY KEY NONCLUSTERED
(
[CMCode],
[HumanCode]
) ON [PRIMARY]
) ON [PRIMARY]
GO


coleling 2011-03-03
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 linares 的回复:]

引用 9 楼 xiaoyehua 的回复:

引用 7 楼 dawugui 的回复:
SQL code
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5……
[/Quote]

再次领略姐的强大!
Linares 2011-03-03
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 xiaoyehua 的回复:]

引用 7 楼 dawugui 的回复:
SQL code
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?



发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

顶乌龟
[/Quote]

水平问题的华丽借口



-- 查出不可转换的
select * from dbo.Archives_CM where (right(villagecode,4)+homecode) like '%[^0-9]%'

-- 更新可转换的
update dbo.Archives_CM set id=cast((right(villagecode,4)+homecode) as bigint)
where (right(villagecode,4)+homecode) not like '%[^0-9]%'


isnumeric是可转换的必要条件,不是充要条件。
查询数据库的状态信息: ------------------------------Data file size---------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') drop table #dbsize create table #dbsize (Dbname varchar(30),dbstatus varchar(20),Recovery_Model varchar(10) default ('NA'), file_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),Free_Space_MB decimal(20,2) default (0)) go insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) exec sp_msforeachdb 'use [?]; select DB_NAME() AS DbName, CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), sum(size)/128.0 AS File_Size_MB, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB from sys.database_files where type=0 group by type' go -------------------log size-------------------------------------- if exists (select * from tempdb.sys.all_objects where name like '#logsize%') drop table #logsize create table #logsize (Dbname varchar(30), Log_File_Size_MB decimal(20,2)default (0),log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0)) go insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) exec sp_msforeachdb 'use [?]; select DB_NAME() AS DbName, sum(size)/128.0 AS Log_File_Size_MB, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB from sys.database_files where type=1 group by type' go --------------------------------database free size if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') drop table #dbfreesize create table #dbfreesize (name varchar(50), database_size varchar(50), Freespace varchar(50)default (0.00)) insert into #dbfreesize(name,database_size,Freespace) exec sp_msforeachdb 'use ?;SELECT database_name = db_name() ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') ,''unallocated space'' = ltrim(str(( CASE WHEN dbsize >= reservedpages THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 ELSE 0 END ), 15, 2) + '' MB'') FROM ( SELECT dbsize = sum(convert(BIGINT, CASE WHEN type = 0 THEN size ELSE 0 END)) ,logsize = sum(convert(BIGINT, CASE WHEN type <> 0 THEN size ELSE 0 END)) FROM sys.database_files ) AS files ,( SELECT reservedpages = sum(a.total_pages) ,usedpages = sum(a.used_pages) ,pages = sum(CASE WHEN it.internal_type IN ( 202 ,204 ,211 ,212 ,213 ,214 ,215 ,216 ) THEN 0 WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id ) AS partitions' ----------------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') drop table #alldbstate create table #alldbstate (dbname varchar(25), DBstatus varchar(25), R_model Varchar(20)) --select * from sys.master_files insert into #alldbstate (dbname,DBstatus,R_model) select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases --select * from #dbsize insert into #dbsize(Dbname,dbstatus,Recovery_Model) select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' insert into #logsize(Dbname) select dbname from #alldbstate where DBstatus <> 'online' insert into #dbfreesize(name) select dbname from #alldbstate where DBstatus <> 'online' select d.Dbname,d.dbstatus,d.Recovery_Model, (file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace from #dbsize d join #logsize l on d.Dbname=l.Dbname join #dbfreesize fs on d.Dbname=fs.name order by Dbname

34,571

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧