27,579
社区成员
发帖
与我相关
我的任务
分享
create table #tb(Telephone nvarchar(50))
insert into #tb
select '17234307473' union all
select '15234354343' union all
select '13234359727' union all
select N'无' union all
select '15234324072' union all
select '13234353937'
update #tb set Telephone= case when isnumeric(Telephone)=1 then convert(nvarchar,convert(bigint,Telephone)+1) else Telephone end
select * from #tb
Telephone
1 17234307474
2 15234354344
3 13234359728
4 无
5 15234324073
6 13234353938
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Telephone] NVARCHAR(50))
Insert #T
select '17234307473' union all
select '15234354343' union all
select '13234359727' union all
select '15234324072' union all
select '13234353937'
Go
--测试数据结束
UPDATE #T
SET Telephone = CONVERT(NVARCHAR, CONVERT(BIGINT, Telephone) + 1);
SELECT * FROM #T
UPDATE ttt
SET Telephone = LEFT(Telephone, 6) + RIGHT('0'
+ CAST(CAST(RIGHT(Telephone, 5) AS INT)
+ 1 AS NVARCHAR(5)), 5);
以这个为准吧,刚才有一种情况未考虑在内
UPDATE ttt
SET Telephone = LEFT(Telephone, 6)
+ CAST(CAST(RIGHT(Telephone, 5) AS INT) + 1 AS NVARCHAR(5));
UPDATE ttt SET Telephone= CONVERT(NVARCHAR(11),CONVERT(BIGINT,Telephone)+1)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Telephone] NVARCHAR(100))
Insert #T
select '17234307473' union all
select '15234354343' union all
select '13234359727' union all
select '15234324072' union all
select '13234353937'
Go
--测试数据结束
UPDATE #T
SET Telephone = CONVERT(NVARCHAR, CONVERT(BIGINT, Telephone) + 1);
SELECT * FROM #T