这是news表中的一条新闻的内容[content字段]:
<P align=center><IMG src="/tp/news/1.jpg" border=0></P>
<p>a</p>
<P align=center><IMG src="/tp/news/2.jpg" border=0></P>
<p>b</p>
<P align=center><IMG src="/tp/news/3.jpg" border=0></P>
<p>c</p>
<P align=center><IMG src="/tp/news/4.jpg" border=0></P>
<p>d</p>
<P align=center><IMG src="/tp/news/5.jpg" border=0></P>
<p>e</p>想写一个函数把这个表里面的所有图片的路径存放到news_tp字段(以后文章中不论有多少张图片的路径就取多少张):
形如:/tp/news/1.jpg,/tp/news/2.jpg,/tp/news/3.jpg,/tp/news/4.jpg,/tp/news/5.jpg
<P align=center><IMG src="/tp/news/1.jpg" border=0></P>
<p>a</p>
<P align=center><IMG src="/tp/news/2.jpg" border=0></P>
<p>b</p>
<P align=center><IMG src="/tp/news/3.jpg" border=0></P>
<p>c</p>
<P align=center><IMG src="/tp/news/4.jpg" border=0></P>
<p>d</p>
<P align=center><IMG src="/tp/news/5.jpg" border=0></P>
<p>e</p>想写一个函数把这个表里面的所有图片的路径存放到news_tp字段(以后文章中不论有多少张图片的路径就取多少张):
形如:/tp/news/1.jpg,/tp/news/2.jpg,/tp/news/3.jpg,/tp/news/4.jpg,/tp/news/5.jpg
(
ID int identity(1,1),
News varchar(8000),
news_tp varchar(8000)
)
insert into #News(News) values('
<P align=center> <IMG src="/tp/news/1.jpg" border=0> </P>
<p>a </p>
<P align=center> <IMG src="/tp/news/2.jpg" border=0> </P>
<p>b </p>
<P align=center> <IMG src="/tp/news/3.jpg" border=0> </P>
<p>c </p>
<P align=center> <IMG src="/tp/news/4.jpg" border=0> </P>
<p>d </p>
<P align=center> <IMG src="/tp/news/5.jpg" border=0> </P>
<p>e </p> ')select * from #news
declare @id int
declare @News varchar(8000)
declare xlcur cursor
for select id,News from #news
open xlcur
fetch xlcur into @id,@News
while @@fetch_status = 0
begin
declare @jpg varchar(8000)
declare @endindex int
declare @startindex int
set @startindex = 1
set @endindex = 0
set @jpg = ''
while @startindex > 0
begin set @startindex = charindex ('/tp/news/',@News,@startindex)
set @endindex = charindex('" border=0>',@News,@endindex)
declare @tempJpg varchar(8000)
set @tempJpg = substring(@News,@startindex,@endindex-@startindex)
if(@tempJpg <> '')
begin
if(@jpg<> '')
begin
set @jpg =@jpg + ',' + @tempJpg
end
else
begin
set @jpg = @tempJpg
end
set @News =substring(@News,@endindex,len(@news) - @endindex)
end
end
print @jpg
update #news set news_tp = @jpg where id = @id
fetch next from xlcur into @id,@News
end
close xlcur
deallocate xlcur