找出含有某个字符的最大id

数据库中有字段
id pathcode
1 null
2 /1
3 /1/2
4 /1/2
5 /1/2
6 /1/2/4
7 /1/2/4
8 /1/2/4/5

找出数据库中含有2个 "/" 最大id,此数据中最大id=5 ,请问Sql语句怎么写？

SQL code

create table tb
(id int,
 pathcode varchar(50)
)
insert into tb values(1,'')
insert into tb values(2,'/1')
insert into tb values(3,'/1/2')
insert into tb values(4,'/1/2')
insert into tb values(5,'/1/2')
insert into tb values(6,'/1/2/4')
insert into tb values(7,'7 /1/2/4')
insert into tb values(8,'/1/2/4/5')

 

select max(id)    from tb

 where   len(pathcode) - len(replace(pathcode,'/',''))  =2
 
-----------
5

(1 行受影响)

select max(id) id from tb where len(pathcode) - len(replace(pathcode,'/','')) = 2

SQL code

--楼主是要查询id的最大，还是pathcode拆分后的最大数字
--下面写的这个是取pathcode拆分后的最大数字
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 pathcode varchar(10)
)
go
insert into tb(pathcode)
select null union all
select '/1' union all
select '/1/2' union all
select '/1/2' union all
select '/1/2' union all
select '/1/2/4' union all
select '/1/2/4' union all
select '/1/2/4/5'
go
select max(cast(reverse(substring(reverse(pathcode),1,charindex('/',reverse(pathcode))-1)) as int)) from tb where len(pathcode)-len(replace(pathcode,'/',''))>1
/*
-----------
5

(1 行受影响)
*/

select max(id) as id from tb where len(pathcode)-len(replace(pathcode,'/','')) = 2