原数据:字符串编号a200
a205
a1005
v1008
c1006
a1007
c1020
b1009
a1009返回最大+1值(这里应该返回C1021)
==========求最大值解决方案(仅供参考)===========
Select top 1 字符串编号 from tb order by cast(substring([字符串编号],patindex('%[0-9]%',[字符串编号]),len([字符串编号])-patindex('%[0-9]%',[字符串编号])+1)as int) desc希望返回最大+1值.也就是返回c1021
a205
a1005
v1008
c1006
a1007
c1020
b1009
a1009返回最大+1值(这里应该返回C1021)
==========求最大值解决方案(仅供参考)===========
Select top 1 字符串编号 from tb order by cast(substring([字符串编号],patindex('%[0-9]%',[字符串编号]),len([字符串编号])-patindex('%[0-9]%',[字符串编号])+1)as int) desc希望返回最大+1值.也就是返回c1021
drop table tb
Go
Create table tb([编号] nvarchar(5))
Insert tb
select N'a200' union all
select N'a205' union all
select N'a1005' union all
select N'v1008' union all
select N'c1006' union all
select N'a1007' union all
select N'c1020' union all
select N'b1009' union all
select N'a1009'
Go
Select top 1
[Next]=left([编号],patindex('%[0-9]%',[编号])-1)+ltrim(
max(cast(right([编号],len([编号])-patindex('%[0-9]%',[编号])+1)as int))+1)
from tb
group by left([编号],patindex('%[0-9]%',[编号])-1)
order by max(cast(right([编号],len([编号])-patindex('%[0-9]%',[编号])+1)as int))+1
desc
/*
Next
-----------------
c1021(1 個資料列受到影響)
*/
from tb
order by cast(substring([字符串编号],patindex('%[0-9]%',[字符串编号]),len([字符串编号])-patindex('%[0-9]%',[字符串编号])+1)as int) desc
select top 1 col
from T
where isnumeric(Reverse(Left( reverse(col), case when patindex('%[^0-9]%', reverse(col))=0 then len(col) else patindex('%[^0-9]%', reverse(col))-1 end )))=1
order by convert(numeric, Reverse(Left( reverse(col), case when patindex('%[^0-9]%', reverse(col))=0 then len(col) else patindex('%[^0-9]%', reverse(col))-1 end ))) desc
drop table tb
Go
Create table tb([编号] nvarchar(5))
Insert tb
select N'a200' union all
select N'a205' union all
select N'a1005' union all
select N'v1008' union all
select N'c1006' union all
select N'a1007' union all
select N'c1020' union all
select N'b1009' union all
select N'a1009'
Go
--如果不同類型的
Select
[Next]=left([编号],patindex('%[0-9]%',[编号])-1)+ltrim(
max(cast(right([编号],len([编号])-patindex('%[0-9]%',[编号])+1)as int))+1)
from tb
group by left([编号],patindex('%[0-9]%',[编号])-1)
order by max(cast(right([编号],len([编号])-patindex('%[0-9]%',[编号])+1)as int))+1
desc
/*
Next
-----------------
c1021
a1010
b1010
v1009(4 個資料列受到影響)
*/
--> 测试数据: @t
declare @t table (c1 varchar(5))
insert into @t
select 'a200' union all
select 'a205' union all
select 'a1005' union all
select 'v1008' union all
select 'c1006' union all
select 'a1007' union all
select 'c1020' union all
select 'b1009' union all
select 'a1009'select top 1 substring(c1,1,1)+rtrim(cast (substring(c1,2,len(c1)-1) as int)+1) [c1]
from @t a
order by cast (substring(c1,2,len(c1)-1) as int) descc1
--------------
c1021
??
drop table tb
Go
Create table tb([编号] nvarchar(5))
Insert tb
select N'a200' union all
select N'a205' union all
select N'a1005' union all
select N'v1008' union all
select N'c1006' union all
select N'a1007' union all
select N'c1020' union all
select N'b1009' union all
select N'a1009'declare @i int
declare @j int
set @i=(select max(cast(substring([编号],2,len([编号])-1) as int)) from tb)
set @j=@i+1
--select @i
select substring([编号],1,1)+ltrim(@j) from tb where cast(substring([编号],2,len([编号])-1) as int)=@i-------------
c1021(1 行受影响)