无论是使用,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 时出错。
还是
update dbo.Archives_CM set id=cast((right(villagecode,4)+homecode) as bigint)都是报一样的错,
将数据类型 varchar 转换为 bigint 时出错。
确定表达式是否为一个有效的数字类型。语法
ISNUMERIC ( expression )参数
expression要计算的表达式。返回类型
int注释
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例
A. 使用 ISNUMERIC
下面的示例返回 1,这是因为 zip 列包含有效的数值。 USE pubs
SELECT ISNUMERIC(zip)
FROM authors
GOB. 使用 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)
楼主要看看是不是所有的villagecode的前四位能够转换为bigint类型!
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
水平问题的华丽借口
-- 查出不可转换的
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是可转换的必要条件,不是充要条件。
[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
实在不行这样试试,或者别用BIGING,用INT
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
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