二级部门和三级部门

--查出组织的二级部门和三级部门

select DISTINCT substring(fullname,
charindex('/',fullname,charindex('/',fullname)+1)+1,   --从第二个/开始
(charindex('/',fullname,                                  --取2 3 间的长度 4－2
 charindex('/',fullname,
  charindex('/',fullname,
   charindex('/',fullname)+1)+1)+1)+1)-
 (charindex('/',fullname,charindex('/',fullname)+1)+1)-1) AS FullName
from org_unit
where len(fullname)-len(REPLACE(fullname,'/',''))>=4
union
select DISTINCT  substring(fullname,
charindex('/',fullname,charindex('/',fullname)+1)+1,     --从第二个/+1开始 
 charindex('/',fullname,
  charindex('/',fullname,
   charindex('/',fullname)+1)+1)-
(charindex('/',fullname,charindex('/',fullname)+1)+1)) AS Fullname
 from org_unit
where len(fullname)-len(REPLACE(fullname,'/',''))>=3

--===================================================================
更正
select  
case when len(fullname)-len(REPLACE(fullname,'/',''))=3 then
  substring(fullname,
  charindex('/',fullname,charindex('/',fullname)+1)+1,     --从第二个/+1开始
   len(fullname)- 
    charindex('/',fullname,
     charindex('/',fullname)+1)
  )
 when len(fullname)-len(REPLACE(fullname,'/',''))=2 then
  substring(fullname,
  charindex('/',fullname,charindex('/',fullname)+1)+1,     --从第二个/+1开始 
   len(fullname)-charindex('/',fullname,charindex('/',fullname)+1)+1
  )
 when len(fullname)-len(REPLACE(fullname,'/',''))>3 then

  substring(fullname,
  charindex('/',fullname,charindex('/',fullname)+1)+1,     --从第二个/+1开始 
   charindex('/',fullname,
    charindex('/',fullname,
     charindex('/',fullname,
      charindex('/',fullname)+1)+1)+1)-
  (charindex('/',fullname,charindex('/',fullname)+1)+1))
end
from org_unit