select max(cast(replace(编号,'a','') as int) from ta
create table #TT ( col nvarchar(50) ) insert into #TT select 'a100' insert into #TT select 'a101' insert into #TT select 'a102' insert into #TT select 'a103' insert into #TT select 'a105' insert into #TT select 'a207' insert into #TT select 'a1022'select max(cast(replace(col,'a','') as int)) from #TT
create table #TT ( col nvarchar(50) ) insert into #TT select 'a100' insert into #TT select 'a101' insert into #TT select 'a102' insert into #TT select 'a103' insert into #TT select 'a105' insert into #TT select 'a207' insert into #TT select 'a1022'select max(cast(replace(col,'a','') as int)) from #TT ----------- 1022(1 行受影响)
select top 1 编号 from tb order by cast(replace(编号,'a','') as int) desc
select max(cast(replace(编号,'a','') as int)) from ta支持二楼
--> Title : Generating test data --> Author : wufeng4552 --> Date : 2010-03-01 10:36:07 if not object_id('tb') is null drop table tb Go Create table tb([编号] nvarchar(5)) Insert tb select N'a100' union all select N'a102' union all select N'a103' union all select N'a105' union all select N'a107' union all select N'a207' union all select N'a1022' Go Select [max]=max(cast(substring([编号],patindex('%[0-9]%',[编号]),len([编号])-patindex('%[0-9]%',[编号])+1)as int))from tb /* max ----------- 1022(1 個資料列受到影響) */
(
col nvarchar(50)
)
insert into #TT select 'a100'
insert into #TT select 'a101'
insert into #TT select 'a102'
insert into #TT select 'a103'
insert into #TT select 'a105'
insert into #TT select 'a207'
insert into #TT select 'a1022'select max(cast(replace(col,'a','') as int)) from #TT
(
col nvarchar(50)
)
insert into #TT select 'a100'
insert into #TT select 'a101'
insert into #TT select 'a102'
insert into #TT select 'a103'
insert into #TT select 'a105'
insert into #TT select 'a207'
insert into #TT select 'a1022'select max(cast(replace(col,'a','') as int)) from #TT
-----------
1022(1 行受影响)
select max(cast(replace(编号,'a','') as int)) from ta支持二楼
--> Author : wufeng4552
--> Date : 2010-03-01 10:36:07
if not object_id('tb') is null
drop table tb
Go
Create table tb([编号] nvarchar(5))
Insert tb
select N'a100' union all
select N'a102' union all
select N'a103' union all
select N'a105' union all
select N'a107' union all
select N'a207' union all
select N'a1022'
Go
Select
[max]=max(cast(substring([编号],patindex('%[0-9]%',[编号]),len([编号])-patindex('%[0-9]%',[编号])+1)as int))from tb
/*
max
-----------
1022(1 個資料列受到影響)
*/