现在想到有两个方法:
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出结果的方法么?
