如果我们假设可以有多个 span 标签，和 </span> 需要删除，然后我们必须做一些丑陋的sql。正如我在评论中所说，理想情况下不要使用t-sql来实现这一点，而是使用支持regex的语言：

WITH YourTable AS(
    SELECT *
    FROM (VALUES('C<sub>7</sub>H<sub><span style="font-size: 10px;">8</sub>O<sub>3</sub>S.H<sub>2</sub>O'),
                ('C<sub>7</sub>H<sub><span style="font-size: 12px;">8</sub>O<sub>3</sub></span>S.H<sub>2</sub>O'),
                ('C<sub>7</sub>H<sub><span style="font-size: 8px; font-style:arial">8</sub>O<sub>3</sub></span>S.H<sub>2</sub>O'),
                ('C<sub>7</sub>H<sub><span style="font-size: 12px;">8</sub>O<sub>3</sub></span><span style="font-size: 8px;">S.H</span><sub>2</sub>O'),
                ('C<sub>7</sub>H<sub>8</sub>O<sub>3</sub>S.H<sub>2</sub>O'))V(YourString)),
rCTE AS(
    SELECT YT.YourString,
           V.CI,
           CASE V.CI WHEN 0 THEN YT.YourString ELSE STUFF(YT.YourString,V.CI,CHARINDEX('>',YT.YourString,V.CI) - V.CI +1 ,'') END AS NewString,
           1 AS Iteration
    FROM YourTable YT
         CROSS APPLY (VALUES(CHARINDEX('<span',YT.YourString)))V(CI)
    UNION ALL
    SELECT r.YourString,
           V.CI,
           CASE V.CI WHEN 0 THEN r.NewString ELSE STUFF(r.NewString,V.CI,CHARINDEX('>',r.NewString,V.CI) - V.CI +1 ,'') END AS NewString,
           r.Iteration + 1
    FROM rCTE r
         CROSS APPLY (VALUES(CHARINDEX('<span',r.NewString)))V(CI)
    WHERE V.CI > 0)
SELECT TOP 1 WITH TIES
       REPLACE(NewString,'</span>','')
FROM rCTE
ORDER BY ROW_NUMBER() OVER (PARTITION BY YourString ORDER BY Iteration DESC);

我同意拉努的观点。span标记可以有其他值。考虑到字符串可能有多个span标记，我们可以在下面尝试

DECLARE @String VARCHAR(MAX) = 'C<sub>7</sub>H<sub><span style="font-size: 10px;">8</sub>O<sub>3</sub>S.H<sub>2</sub>O'

;WITH CTE AS
(
    SELECT @String AS Formula
    WHERE @String LIKE '%<span%'

    UNION ALL

    SELECT STUFF(Formula, CHARINDEX('<span', Formula, 1), CHARINDEX('>', Formula, CHARINDEX('<span', Formula, 1))-CHARINDEX('<span', Formula, 1)+1, '') 
    FROM CTE
    WHERE Formula LIKE '%<span%'

)
SELECT TOP 1 Formula 
FROM CTE
ORDER BY LEN(Formula)