一个字符串查找的问题,希望找个好的解决方法

[shoppo0505]

给入一个字符串,然后我需要通过语句,在某一列中查找所有含有所有这些字符串的数据
现在想到有两个方法:

1. 将给入的字符串拆解放入表格中,然后拼接sql语句再执行,代码如下
create table #tosearch (Search nvarchar(100))
insert into #tosearch
select 'au'
--union all select 'be'
union all select 'de'

create table #t (Name nvarchar(100))

insert into #t
select 'Haus' union all
select 'Haus to be' union all
select 'Haus destory' union all
select 'Haus to be destory'

declare @sql nvarchar(1000) = 'select * from #t where'
declare @constrains nvarchar(1000) = ''
select @constrains = @constrains + ' Name like ''%' + Search + '%'' and' from #tosearch

select LEFT(@sql + @constrains, LEN(@sql + @constrains)-4)
set @sql = LEFT(@sql + @constrains, LEN(@sql + @constrains)-4)
exec (@sql)

drop table #tosearch
drop table #t

2.将所有数据放入临时表中,然后便利所有要查找的字符串不含有就删除
大致代码如下:
create table #tosearch (Search nvarchar(100))
insert into #tosearch
select 'au'
union all select 'be'
union all select 'de'

create table #t1 (Name nvarchar(100))

insert into #t1
select 'Haus' union all
select 'Haus to be' union all
select 'Haus destory' union all
select 'Haus to be destory'

select * into #t2 from #t1

declare @s nvarchar(100)
while (SELECT COUNT(*) from #tosearch) > 0
begin
set @s = (SELECT top 1 Search from #tosearch)
delete from #t2 where charindex(@s, Name) = 0
set rowCount 1
delete from #tosearch
set rowCount 0
end

select * from #t2

drop table #tosearch
drop table #t1
drop table #t2


总结:两种方法都不满意.
方法1:考虑到SQL注入式攻击,彻底pass
方法2:对于大数据量,性能肯定不好

有谁有不用拼接sql语句,直接select出结果的方法么?

[shoppo0505]

注意:0楼只是一个例子,分解后存入临时表的数据行数不确定

[shoppo0505]

引用 2 楼 ch21st 的回复:

你第一顶想法我修改了写法，没有使用动态语句，你参考下：

create table #tosearch (Search nvarchar(100))
insert into #tosearch
select 'au' 
--union all select 'be'
union all select 'de'

create table #t  (Name nvarchar(100))

insert into #t
select 'Haus' union all
select 'Haus to be' union all
select 'Haus destory' union all
select 'Haus to be destory'
-------------------  修改-------------------
SELECT DISTINCT a.Name FROM (
    SELECT t.*,SUM( CASE WHEN CHARINDEX(t1.Search,t.Name)>0 THEN 1 ELSE 0 END )OVER(PARTITION BY t.Name) as MatchedCount,COUNT(0)OVER(PARTITION BY t.Name) AS SerchCount
    FROM #t AS t,#tosearch AS t1
) a WHERE a.MatchedCount=a.SerchCount
/*结果
Name
Haus destory
Haus to be destory
*/

drop table #tosearch
drop table #t

谢谢这个方法有改进,但是distinct 降低了效率

[道素]

你的数据很多吗？ 换另外一种写法，但我不确定效率会否有改进，只是不需要distinct

SELECT * FROM #t AS t
CROSS APPLY( 
	SELECT * FROM (
	SELECT 'notmatched' AS flg, t.Name,CASE WHEN CHARINDEX(ts.Search,t.Name)=0 THEN 1 ELSE 0 END AS notfound FROM #tosearch AS ts 
	) p1 PIVOT(SUM(notfound) for flg IN ([notmatched]))p2
) s WHERE s.[notmatched]=0

[shoppo0505]

引用 4 楼 ch21st 的回复:

你的数据很多吗？ 换另外一种写法，但我不确定效率会否有改进，只是不需要distinct

SELECT * FROM #t AS t
CROSS APPLY( 
	SELECT * FROM (
	SELECT 'notmatched' AS flg, t.Name,CASE WHEN CHARINDEX(ts.Search,t.Name)=0 THEN 1 ELSE 0 END AS notfound FROM #tosearch AS ts 
	) p1 PIVOT(SUM(notfound) for flg IN ([notmatched]))p2
) s WHERE s.[notmatched]=0

5千万级别

[74w20]

create table #tosearch (Search nvarchar(100))
insert into #tosearch
select 'be'
union all select 'de'

create table #t1  (Name nvarchar(100))

insert into #t1
select 'Haus' union all
select 'Haus to be' union all
select 'Haus destory' union all
select 'Haus to be destory'


SELECT * FROM #t1 a
WHERE NOT EXISTS(SELECT top 1 'x' FROM #tosearch b WHERE a.Name LIKE '%'+b.Search+'%')
drop table #tosearch
drop table #t1

这个是sql server的写法

[道素]

你第一顶想法我修改了写法，没有使用动态语句，你参考下：

create table #tosearch (Search nvarchar(100))
insert into #tosearch
select 'au' 
--union all select 'be'
union all select 'de'

create table #t  (Name nvarchar(100))

insert into #t
select 'Haus' union all
select 'Haus to be' union all
select 'Haus destory' union all
select 'Haus to be destory'
-------------------  修改-------------------
SELECT DISTINCT a.Name FROM (
    SELECT t.*,SUM( CASE WHEN CHARINDEX(t1.Search,t.Name)>0 THEN 1 ELSE 0 END )OVER(PARTITION BY t.Name) as MatchedCount,COUNT(0)OVER(PARTITION BY t.Name) AS SerchCount
    FROM #t AS t,#tosearch AS t1
) a WHERE a.MatchedCount=a.SerchCount
/*结果
Name
Haus destory
Haus to be destory
*/

drop table #tosearch
drop table #t

[shoppo0505]

我在服务器,2200万行数据的表格上测试了下,执行时间1分40秒这个效率太低

[Tiger_Zhao]

用普通的字符串查找/匹配总是要遍历全部的记录。
如果你查找的内容是独立的单词，应该试试全文索引（全文搜索入门）。
查询语句格式为

SELECT *
  FROM 表
 WHERE CONTAINS(字段名, '单词1 AND 单词2')

[中国风]

如果数据量大时用LIKE模糊找性能低，要求不高用数据库的全文检索，要求高可以中间件去做如CORESEEK/SPHINX

[道素]

引用 5 楼 shoppo0505 的回复:

[quote=引用 4 楼 ch21st 的回复:] 你的数据很多吗？ 换另外一种写法，但我不确定效率会否有改进，只是不需要distinct

SELECT * FROM #t AS t
CROSS APPLY( 
	SELECT * FROM (
	SELECT 'notmatched' AS flg, t.Name,CASE WHEN CHARINDEX(ts.Search,t.Name)=0 THEN 1 ELSE 0 END AS notfound FROM #tosearch AS ts 
	) p1 PIVOT(SUM(notfound) for flg IN ([notmatched]))p2
) s WHERE s.[notmatched]=0

5千万级别[/quote] 我用下面的方法查15万数据用了1毫秒

SELECT * FROM #t1 a
WHERE NOT EXISTS(SELECT 0 FROM #tosearch b WHERE CHARINDEX(b.Search,a.Name)=0)