The column in this you care about is 'Final Removal'.

I broke it down so you can see what each component of the function is doing.

Please test and confirm this will work for you. Notice I work backwards through the string so I don't have to constantly repeat previous work (like removing the anchor tag).

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

Also, note how I created consumable test data at the beginning. This will help us help you in the future, if you do that.