declare @string varchar(100),
	@start int,
	@end int

set @string = 'please follow <a href="http://www.hello.com">this link</a> or <a href="somepage.asp">this link</a>'

-----------------------------------------------
-- find beginning position of the '<' in '<a'
-----------------------------------------------
select @start = charindex('<a', @string) 

-----------------------------------------------
-- find ending position of the '>' in '</a>'
-----------------------------------------------
select @end = charindex('</a>',@string) + len('</a>') 

-----------------------------------------------
-- use substring func to extract lenghth between
-----------------------------------------------
select substring(@string, @start, (@end - @start))

declare @string varchar(1000),
	@start int,
	@end int,
	@url varchar(8000)

set @string = 'please follow <a href="http://www.hello.com">this link</a> or <a href="somepage.asp">this link</a> or <a href="onemore.asp"</a>'

select 	@start = charindex('<a', @string),
	@end = charindex('</a>',@string) + len('</a>'),
	@url = ''

while @start <> 0
begin
	-- print @string

	-- extract loop, comma separate if not blank
	select @url = 	case
				when @url <> '' then  @url + ', ' + substring(@string, @start, @end - @start)
				else @url + substring(@string, @start, (charindex('</a>',@string) + len('</a>') - @start))
			end
	-- remove extracted url from @string
	select @string = stuff(@string, @start, @end - @start, '')


	-- support loop
	select 	@start = charindex('<a', @string),
		@end = charindex('</a>',@string) + len('</a>')

end
select @url