select max(id) from tablename name where not (patindex('[a-Z][a-Z]%',id)>0)
create table da_xiao (a varchar(20)) insert into da_xiao select 'AA001' insert into da_xiao select 'A0001' insert into da_xiao select '00001'SELECT * FROM da_xiao WHERE substring(a,1,2) like '[a-z][A-Z]' /* AA001 */
create table da_xiao (a varchar(20)) insert into da_xiao select 'AA001' insert into da_xiao select 'A0001' insert into da_xiao select '00001' insert into da_xiao select '00002' insert into da_xiao select 'CC002'select max(a) from da_xiao where ISNUMERIC(left(a,2))>0-------------------- 00002(1 行受影响)
select max(ID) from 表A where ID like '[^a-z][^a-z]%'
-->测试数据: #T create table #T (ID varchar(5)) insert into #T select 'AA001' union all select 'BB001' union all select '00001' union all select '00002' union all select 'CC002'select max(ID) from #T where ID like '[^a-z][^a-z]%'/* 00002 */-->删除测试 drop table #T
SELECT * FROM da_xiao WHERE substring(a,1,2) not like '[a-z][A-Z]'
CREATE TABLE [tblName] ( [id] [varchar] (50) COLLATE Japanese_CI_AS NULL , [name] [varchar] (50) COLLATE Japanese_CI_AS NULL ) ON [PRIMARY] GOinsert into tblName values('1','2') insert into tblName values('12a','2') insert into tblName values('1a','2') insert into tblName values('AA001','2') insert into tblName values('BB001','2') insert into tblName values('00001','2') insert into tblName values('00002','2') insert into tblName values('CC002','2') SELECT max(id) FROM dbo.tblName WHERE substring(id,1,2) not like '[a-z][A-Z]%' and substring(id,1,2) not like '[0-9][A-Z,a-z]%'
not (patindex('[a-Z][a-Z]%',id)>0)
create table da_xiao
(a varchar(20))
insert into da_xiao select 'AA001'
insert into da_xiao select 'A0001'
insert into da_xiao select '00001'SELECT * FROM da_xiao WHERE substring(a,1,2) like '[a-z][A-Z]'
/*
AA001
*/
create table da_xiao
(a varchar(20))
insert into da_xiao select 'AA001'
insert into da_xiao select 'A0001'
insert into da_xiao select '00001'
insert into da_xiao select '00002'
insert into da_xiao select 'CC002'select max(a) from da_xiao where ISNUMERIC(left(a,2))>0--------------------
00002(1 行受影响)
create table #T (ID varchar(5))
insert into #T
select 'AA001' union all
select 'BB001' union all
select '00001' union all
select '00002' union all
select 'CC002'select max(ID) from #T where ID like '[^a-z][^a-z]%'/*
00002
*/-->删除测试
drop table #T
[id] [varchar] (50) COLLATE Japanese_CI_AS NULL ,
[name] [varchar] (50) COLLATE Japanese_CI_AS NULL
) ON [PRIMARY]
GOinsert into tblName values('1','2')
insert into tblName values('12a','2')
insert into tblName values('1a','2')
insert into tblName values('AA001','2')
insert into tblName values('BB001','2')
insert into tblName values('00001','2')
insert into tblName values('00002','2')
insert into tblName values('CC002','2') SELECT max(id) FROM dbo.tblName WHERE substring(id,1,2) not like '[a-z][A-Z]%'
and substring(id,1,2) not like '[0-9][A-Z,a-z]%'