我做一个新闻发布系统,现在把网页的HTML内容存在数据库里,如下:
字段:news_Content
内容:
<P> </P> <P>123</P> <P>12<IMG height=25 src="/WebSite/upfiles/2721.gif" width=50 border=0></P> <P>312</P> <P>312</P> <P> </P>
<P> </P> <P>123</P> <P>12<IMG height=48 src="/WebSite/upfiles/2696.gif" width=40 border=0></P> <P>312</P> <P>312</P> <P> </P>
<P> </P> <P>123</P> <P>12</P> <P>312</P> <P>312</P> </P>
我怎样才能把该字段下的内容含有<IMG>标签的值src取出来,结果如下
字段:src
内容:
src=/WebSite/upfiles/2721.gif
src=/WebSite/upfiles/2696.gif
------解决方案--------------------
- SQL code
declare @tb table (s varchar(1000))insert into @tb select '<P> </P> <P>123 </P> <P>12 <IMG height=25 src="/WebSite/upfiles/2721.gif" width=50 border=0> </P> <P>312 </P> <P>312'select substring(s,patindex('%src="%',s),charindex('"',s,patindex('%src="%',s)+5)-(patindex('%src="%',s))+1) from @Tbwhere s like '%src="%'
------解决方案--------------------
- SQL code
if object_id('tempdb.dbo.#') is not null drop table #create table # (c varchar(8000))insert # select '<P> src= </P> <P>123 </P> <P>12 <IMG height=25 src="/WebSite/upfiles/2721.gif" width=50 border=0> </P> <P>312 </P> <P>312 </P> <P> </P>'insert # select '<P> src=" </P> <P>123 </P> <P>12 <IMG height=48 src="/WebSite/upfiles/2696.gif" width=40 border=0> </P> <P>312 </P> <P>312 </P> <P> </P>'insert # select '<P> </P> <P>123 </P> <P>12 </P> <P>312 </P> <P>312 </P> </P>'select * from #select substring(c, charindex('src=',c,charindex('<IMG',c)), charindex('"',c,charindex('src=',c,charindex('<IMG',c))+5)-charindex('src=',c,charindex('<IMG',c))+1) from # where charindex('src=',c,charindex('<IMG',c))>0/*src="/WebSite/upfiles/2721.gif"src="/WebSite/upfiles/2696.gif"*/
------解决方案--------------------
- SQL code
create table # (news_content varchar(8000))insert # select '<P> </P> <P>123 </P> <P>12 <IMG height=25 src="/WebSite/upfiles/2721.gif" width=50 border=0> </P> <P>312 </P> <P>312 </P> <P> </P> 'insert # select '<P> </P> <P>123 </P> <P>12 <IMG height=48 src="/WebSite/upfiles/2696.gif" width=40 border=0> </P> <P>312 </P> <P>312 </P> <P> </P> 'insert # select '<P> </P> <P>123 </P> <P>12 </P> <P>312 </P> <P>312 </P> </P>'goselect replace(substring(news_content,charindex('src="',news_content),charindex('.gif"',news_content) - charindex('src="',news_content)+5),'"','') as src from #where charindex('src="',news_content) > 0 if object_id('tempdb.dbo.#') is not null drop table #/*src ---------src=/WebSite/upfiles/2721.gifsrc=/WebSite/upfiles/2696.gif*/