直接具例子吧,这样应该更容易看明白。数据库:SQL SERVER 2000
表 名:ArticleContents
字段名:ArticleTitleArticleTitle字段内容为:Preliminary Identification of Root Specific-Expressed Protein in <EM>Puccinellia tenuiflora</EM> under NaHCO<SUB>3</SUB> Stress现在我以关键词:“in Puccinellia”进行模糊搜索(select * from articlecontents where ArticleTitle like '%in Puccinellia%' ),结果肯定是没有搜索到,因为字段里存的是“in <EM>Puccinellia”问题:如何忽略掉ArticleTitle字段里的HTML标签(如本例的<EM>)来模糊查询
表 名:ArticleContents
字段名:ArticleTitleArticleTitle字段内容为:Preliminary Identification of Root Specific-Expressed Protein in <EM>Puccinellia tenuiflora</EM> under NaHCO<SUB>3</SUB> Stress现在我以关键词:“in Puccinellia”进行模糊搜索(select * from articlecontents where ArticleTitle like '%in Puccinellia%' ),结果肯定是没有搜索到,因为字段里存的是“in <EM>Puccinellia”问题:如何忽略掉ArticleTitle字段里的HTML标签(如本例的<EM>)来模糊查询
--创建一个去掉<> 的函数:
create function splist(@var varchar(1000))
returns varchar(1000)
as
begin
while(charindex('<',@var)>0)
begin
set @var = stuff(@var,charindex('<',@var),charindex('>',@var)-charindex('<',@var)+1,'')
end
return @var
end
godeclare @ArticleContents table (ArticleTitle varchar(1000))
insert into @ArticleContents
select 'ArticleTitle字段内容为:Preliminary Identification of Root Specific-Expressed Protein in <EM>Puccinellia tenuiflora </EM> under NaHCO <SUB>3 </SUB> Stress'select * from @ArticleContents where dbo.splist(ArticleTitle) like '%in Puccinellia%'
DECLARE @s VARCHAR(100)INSERT @t SELECT 'Protein in <EM>Puccinellia tenuiflora </EM> under NaHCO <SUB>3 </SUB> Stress '
UNION ALL SELECT 'Protein in <BR>Puccinellia tenuiflora </EM> under NaHCO <SUB>3 </SUB> Stress '
UNION ALL SELECT 'Protein in dd Puccinellia tenuiflora </EM> under NaHCO <SUB>3 </SUB> Stress'
UNION ALL SELECT 'in'
SELECT * FROM @t WHERE v LIKE '%in%Puccinelli%' AND PATINDEX('%in%[^<%>]Puccinellia%',v)=0/*
1 Protein in <EM>Puccinellia tenuiflora </EM> under NaHCO <SUB>3 </SUB> Stress
2 Protein in <BR>Puccinellia tenuiflora </EM> under NaHCO <SUB>3 </SUB> Stress */所以建议你放弃。