select distinct substring(url,1,charindex('/',right(url,len(url)-1))) from tb
create table tb(url varchar(20)) insert into tb select '/A/B' insert into tb select '/A/B/C' insert into tb select '/A/D/a.txt' insert into tb select '/E/b.txt' insert into tb select '/E/F' insert into tb select '/G' insert into tb select '/H/I' insert into tb select '/H/J' go select distinct LEFT(url,CHARINDEX('/',url+'/',2)-1) from tb /*-------------------- /A /E /G /H(4 行受影响)*/ go drop table tb
create table tb(url varchar(20)) insert into tb select '/A/B' insert into tb select '/A/B/C' insert into tb select '/A/D/a.txt' insert into tb select '/E/b.txt' insert into tb select '/E/F' insert into tb select '/G' insert into tb select '/H/I' insert into tb select '/H/J' go select distinct LEFT(url,CHARINDEX('/',url+'/',2)-1) from tb --用charindex函数找到第二个'/'(从第2位向后找)的位置,取url左边这个位置减1个字符. --为了一定能找到'/',在charindex函数中的查找对象后加一个'/' /*-------------------- /A /E /G /H(4 行受影响)*/ go drop table tb
Declare @sql varchar(100) ,@i varchar(200) set @sql = '/A/B' select url from [file] where url like @sql+'/%'select distinct LEFT(url,CHARINDEX('/',url+'/',len(@sql)+2)-1) from (select url from [file] where url like @sql+'/%')a
insert into tb select '/A/B'
insert into tb select '/A/B/C'
insert into tb select '/A/D/a.txt'
insert into tb select '/E/b.txt'
insert into tb select '/E/F'
insert into tb select '/G'
insert into tb select '/H/I'
insert into tb select '/H/J'
go
select distinct LEFT(url,CHARINDEX('/',url+'/',2)-1) from tb
/*--------------------
/A
/E
/G
/H(4 行受影响)*/
go
drop table tb
insert into tb select '/A/B'
insert into tb select '/A/B/C'
insert into tb select '/A/D/a.txt'
insert into tb select '/E/b.txt'
insert into tb select '/E/F'
insert into tb select '/G'
insert into tb select '/H/I'
insert into tb select '/H/J'
go
select distinct LEFT(url,CHARINDEX('/',url+'/',2)-1) from tb
--用charindex函数找到第二个'/'(从第2位向后找)的位置,取url左边这个位置减1个字符.
--为了一定能找到'/',在charindex函数中的查找对象后加一个'/'
/*--------------------
/A
/E
/G
/H(4 行受影响)*/
go
drop table tb
set @sql = '/A/B'
select url from [file] where url like @sql+'/%'select distinct LEFT(url,CHARINDEX('/',url+'/',len(@sql)+2)-1) from (select url from [file] where url like @sql+'/%')a