请教一段sql语句，字符串匹配的问题

表结构如下：
C1  C2
1   /a/b/c/d.txt
2   /a/b/cc/d.txt
3   /a/bb/c/d.txt
4   /a/bb/cc/d.html
5   /aa/bb/d.tt
6   /aa/bb/dd.tt

C1是id， C2是文件的路径的一个字段。
根据给定的几个路径，列出所有在这些字段下文件ID。

查询条件是个字符串：“/a/b/c, /a/bb, /aa/bb/d.tt”. 
即所有在“/a/b/c”和“/a/bb”等这些路径下文件都列出来。
结果集合应该是：
C1  C2
1   /a/b/c/d.txt
3   /a/bb/c/d.txt
4   /a/bb/cc/d.html
5   /aa/bb/d.tt

哪位帮个忙，先把临时表创建了,只有60分了：

SELECT 1 pid, '/a/b/c/d.txt' fpath into #t
UNION ALL SELECT 2, '/a/b/cc/d.txt'
UNION ALL SELECT 3, '/a/bb/c/d.txt'
UNION ALL SELECT 4, '/a/bb/cc/d.html'
UNION ALL SELECT 5, '/aa/bb/d.tt'
UNION ALL SELECT 6, '/aa/bb/dd.tt'

13 个解决方案

#1

一个串?“/a/b/c, /a/bb, /aa/bb/d.tt”. 格式固定吗?

#2

都是用逗号分割的，可能有很多部分

#3

create table tb(c1 int , c2 varchar(50))
insert into tb select 1, '/a/b/c/d.txt' 
UNION ALL SELECT 2, '/a/b/cc/d.txt'
UNION ALL SELECT 3, '/a/bb/c/d.txt'
UNION ALL SELECT 4, '/a/bb/cc/d.html'
UNION ALL SELECT 5, '/aa/bb/d.tt'
UNION ALL SELECT 6, '/aa/bb/dd.tt'
go

/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as 
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @inputstr <> '\'
  insert @temp values(@inputstr)
  return 
end
go

--调用
declare @str as varchar(30)
set @str = '/a/b/c,/a/bb,/aa/bb/d.tt'

select * from tb m,
(select * from dbo.fn_split(@str,',')) n
where m.c2 + '/' like '%' + n.a + '/%'

drop table tb
drop function dbo.fn_split 

/*
c1          c2                                                 a            
----------- -------------------------------------------------- -------------
1           /a/b/c/d.txt                                       /a/b/c
3           /a/bb/c/d.txt                                      /a/bb
4           /a/bb/cc/d.html                                    /a/bb
5           /aa/bb/d.tt                                        /aa/bb/d.tt

（所影响的行数为 4 行）

*/

#4

非常感谢，我去看看，这个是函数，我在proc里用字符串拼凑起来“EXEC SP_EXECUTESQL”执行可以么？

#5

引用 4 楼 GetDC 的回复:

非常感谢，我去看看，这个是函数，我在proc里用字符串拼凑起来“EXEC SP_EXECUTESQL”执行可以么？

你试试吧.

我是用了个函数.

#6

create table test(C1 INT,  C2 VARCHAR(100))
INSERT test SELECT 1  ,'/a/b/c/d.txt' 
union all select 2  ,'/a/b/cc/d.txt' 
union all select 3  ,'/a/bb/c/d.txt' 
union all select 4  ,'/a/bb/cc/d.html' 
union all select 5  ,'/aa/bb/d.tt' 
union all select 6  ,'/aa/bb/dd.tt' 

DECLARE @t VARCHAR(100)
SET @t='/a/b/c,/a/bb,/aa/bb/d.tt'

DECLARE @s TABLE(id INT IDENTITY(1,1),a INT)
INSERT @s SELECT TOP 100 0 FROM syscolumns s

SELECT a.*
FROM test a, @s b
WHERE SUBSTRING(','+@t,id,1)=',' AND CHARINDEX(SUBSTRING(@t,id,CHARINDEX(',',@t+',',id)-id)+'/',c2+'/')>0
--result
/*C1          C2                             
----------- ------------------------------ 
1           /a/b/c/d.txt
3           /a/bb/c/d.txt
4           /a/bb/cc/d.html
5           /aa/bb/d.tt

（所影响的行数为 4 行）
*/

#7

帮顶。

#8

6樓頂一下，學習了

#9

引用楼主 GetDC 的帖子:

表结构如下： 
C1  C2 
1  /a/b/c/d.txt 
2  /a/b/cc/d.txt 
3  /a/bb/c/d.txt 
4  /a/bb/cc/d.html 
5  /aa/bb/d.tt 
6  /aa/bb/dd.tt 

C1是id， C2是文件的路径的一个字段。 
根据给定的几个路径，列出所有在这些字段下文件ID。 

查询条件是个字符串：“/a/b/c, /a/bb, /aa/bb/d.tt”. 
即所有在“/a/b/c”和“/a/bb”等这些路径下文件都列出来。 
结果集合应该是： 
C1  C2 
1  /a/b/c/d.txt 
3  /a/bb/c/…

select * from table where c2 like '%/a/b/c%' 
union
select * from table where c2 like '%/a/bb%'

#10

select * from table where c2 like '%,/a/b/c%' 
union
select * from table where c2 like '%,/a/bb%'

#11

请教一下六楼：chuifengde 树上的鸟儿 

表test可能很大千万条数据左右， 用这条语句“FROM test a, @s b”效率会不会很慢？

#12

create table tb(C1 INT,  C2 VARCHAR(100))
INSERT tb SELECT 1  ,'/a/b/c/d.txt' 
union all select 2  ,'/a/b/cc/d.txt' 
union all select 3  ,'/a/bb/c/d.txt' 
union all select 4  ,'/a/bb/cc/d.html' 
union all select 5  ,'/aa/bb/d.tt' 
union all select 6  ,'/aa/bb/dd.tt' 

DECLARE @x xml
SET @x='<item>'+replace('/a/b/c,/a/bb,/aa/bb/d.tt',',','</item><item>')+'</item>'

select a.* from tb a inner join (
select col=t.c.value('.','varchar(max)')+'/' from @x.nodes('//item') as t(c))b on charindex(b.col,a.c2+'/')>0

C1 C2
1 /a/b/c/d.txt
3 /a/bb/c/d.txt
4 /a/bb/cc/d.html
5 /aa/bb/d.tt

#13

引用 12 楼 pl_mm 的回复:

SQL codecreate table tb(C1 INT,  C2 VARCHAR(100))
INSERT tb SELECT 1  ,'/a/b/c/d.txt' 
union all select 2  ,'/a/b/cc/d.txt' 
union all select 3  ,'/a/bb/c/d.txt' 
union all select 4  ,'/a/bb/cc/d.html' 
union all select 5  ,'/aa/bb/d.tt' 
union all select 6  ,'/aa/bb/dd.tt' 

DECLARE @x xml
SET @x='<item>'+replace('/a/b/c,/a/bb,/aa/bb/d.tt',',','</item><item>')+'</item>'

select a.* f…

多谢楼上的，这个方法挺好用，但是在数据量非常大的时候效率有点低， 相当a表和b表列数的乘积。有办法提高效率么？