-- do some testdata
create table Product(ProductID int primary key, ProductDescription text not null)
go
insert Product
select 1000000,'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970''s and 1980''s television and film Productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and Production music.<br><br><span class=''Product-name-no-link''>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a><br>06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a><br>07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a><br>08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a><br>09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a><br>10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a><br>11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a><br>'
insert Product
select 1000001,'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970''s and 1980''s television and film Productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and Production music.<br><br><span class=''Product-name-no-link''>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a><br>06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a><br>07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a><br>08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a><br>09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a><br>10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a><br>11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a><br>'
go

-- create a function to parse the html
create function hardParser
(
	@ProductID int
)
returns @childProducts table(RelatedProductID int)
as
-- hardcoded function that presupposes that the relevant info in the HTML text is:
-- "ProductInfo.aspx?ProductID=105234"
-- loop and search for the text, retreive the number, and put it in the table-valued-function's table
begin
	declare @currIndex int
	select @currIndex = 0
	while 1=1
	begin
		-- magic # 27 is because 'ProductInfo.aspx?ProductID=' is 27 characters long
		select @currindex = charindex('ProductInfo.aspx?ProductID=',ProductDescription,@currindex) + 27 from Product where ProductID = @ProductID
		if @currindex - 27 = 0 or @currindex = 0
			return
		else
		begin
			insert @childProducts
			select cast(substring(ProductDescription,@currIndex,charindex('"',ProductDescription,@currIndex)-@currIndex) as int)
			from Product where ProductID = @ProductID
		end
	end
	return
end
go

------------------ using the function ------------------------------

-- in sql2000, you can get the related products for each product, one by one:
declare @productID int
set @productID = 1000000
select @productID, RelatedProductID from dbo.hardParser(@productID)

-- in sql 2005, you can get all in one step
select	p.ProductID, x.RelatedProductID
from	Product p
	cross apply dbo.hardParser(p.ProductID) as x

--------------------------------------------------------------------

-- cleanup
/*
drop table product -- PS! don\t drop it!!!!
drop function dbo.hardParser
*/