IF OBJECT_ID( 'tempdb..#test') IS NOT NULL
	DROP TABLE #test

CREATE TABLE #test (httpfield VARCHAR(500))

INSERT INTO #test
SELECT
	'acbdsafsdfasd <a href="/blahblah"> blahblah </a> happy days are here again!' UNION ALL SELECT
	'a12312340934241239837458923041029341092 <a href="/yummynummy"> yummynummy </a> xyzalkdfjsa'

SELECT
	PATINDEX(  '%<a href=%', httpfield ) AS StartingChar,
	CHARINDEX( '>', httpfield, PATINDEX(  '%<a href=%', httpfield ) +1) AS EndingBracket,
	STUFF( httpfield, 
				PATINDEX(  '%<a href=%', httpfield ), 
				CHARINDEX( '>', httpfield, PATINDEX(  '%<a href=%', httpfield ) +1) - PATINDEX(  '%<a href=%', httpfield ) + 1,
				''
		) AS RemovedFirstTag,
	CHARINDEX( '</a>', httpfield, PATINDEX(  '%<a href=%', httpfield )) AS AnchorTagStart,
	STUFF( httpfield, CHARINDEX( '</a>', httpfield, PATINDEX(  '%<a href=%', httpfield )) , 4, '') AS RemovedCorrectAnchor,
	STUFF( STUFF( httpfield, CHARINDEX( '</a>', httpfield, PATINDEX(  '%<a href=%', httpfield )) , 4, ''),
				PATINDEX(  '%<a href=%', httpfield ), 
				CHARINDEX( '>', httpfield, PATINDEX(  '%<a href=%', httpfield ) +1) - PATINDEX(  '%<a href=%', httpfield ) + 1,
				''
		) FinalRemoval
FROM
	#test