表BB里有个字段AAA
AAA
12100
12101
13105
22104
22105
31102
31105
怎么更新成如下:
AAA
22100
22101
23105
32104
32105
41102
41105
用这个语句update tb
set aaa = cast(cast(left(aaa,1) as int) + 1 as varchar) + substring(aaa,2,len(aaa)-1)
提示我错误如下:在把varchar转换成int时的语句错误
请问还有别的方法了吗?
AAA
12100
12101
13105
22104
22105
31102
31105
怎么更新成如下:
AAA
22100
22101
23105
32104
32105
41102
41105
用这个语句update tb
set aaa = cast(cast(left(aaa,1) as int) + 1 as varchar) + substring(aaa,2,len(aaa)-1)
提示我错误如下:在把varchar转换成int时的语句错误
请问还有别的方法了吗?
insert @t
select 12100 union all
select 12101 union all
select 13105 union all
select 22104 union all
select 22105 union all
select 31102 union all
select 31105----更新
UPDATE @t SET aaa = STUFF(AAA,1,1,rtrim(CAST(left(AAA,1) AS int) + 1) + left(AAA,1))
----查看
SELECT * FROM @t/*结果
AAA
-----------
212100
212101
213105
322104
322105
431102
431105
*/
set AAA=rtrim(case(left(AAA,1) as int)+1)+stuff(AAA,1,1,'')
要写成varchar(20)
要分配大小
也可这样写
set aaa = cast(cast(aaa as int)+10000 as varchar(10))
insert into # select 12100
insert into # select 12101
insert into # select 32101
insert into # select 32109select reverse(reverse(rtrim(AAA) + '1') + 1)/10 from #22100
22101
42101
42109
declare @t table(AAA varchar(10))
insert @t
select 12100 union all
select 12101 union all
select 13105 union all
select 22104 union all
select 22105 union all
select 31102 union all
select 31105----更新
UPDATE @t SET aaa = rtrim(cast(left(AAA,1) as int)+1)+stuff(AAA,1,1,'')
----查看
SELECT * FROM @t
/*AAA
----------
22100
22101
23105
32104
32105
41102
41105(7 row(s) affected)
*/
Declare @BB Table(AAA Varchar(10))---类型为Varchar
Insert @BB Select 12100
Union All Select 12101
Union All Select 13105
Union All Select 22104
Union All Select 22105
Union All Select 31102
Union All Select 31105
Select * From @BB
---更新
Update @BB Set AAA=Rtrim(Left(AAA,1)+1)+Right(AAA,Len(AAA)-1)
---查看更新后的数据
Select * From @BB
---结果
/*
AAA
-----------
22100
22101
23105
32104
32105
41102
41105(所影响的行数为 7 行)
*/
将 varchar 值 'A' 转换为数据类型为 int 的列时发生语法错误。
还是发生这样的错误!
set @i = 'A2340'select aa =
case when isnumeric(@i) = 1 then cast(reverse('1' + reverse(@i) + 1)/10 as nvarchar(20))
else @i endset @i = '12340'
select aa =
case when isnumeric(@i) = 1 then cast(reverse('1' + reverse(@i) + 1)/10 as nvarchar(20))
else @i end
rtrim(cast(left(AAA,1) as int)+1)+stuff(AAA,1,1,'')
else AAA
end
insert into #
select '12100' union all
select '22101' union all
select '32105' union all
select '42104' union all
select 'A2105' union all
select '#2102' union all
select '!2105'select AAA =
case when isnumeric(AAA) = 1 then cast(reverse('1' + reverse(AAA) + 1)/10 as nvarchar(20))
else AAA end
from #
/*结果
22100
32101
42105
52104
A2105
#2102
!2105
*/