title: Creating an inline table function to strip HTML
body: <p>I'm trying to create an inline table function to strip out HTML (so that I can <code>CROSS APPLY</code> it with another table). We have a scalar function in place, but the performance is just not good enough. </p>
  
  <p>I'm really new to using T-SQL, so I don't know that many good practices of it, so if anyone has any suggestions, please let me know! </p>
  
  <p>This is what I have so far:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE FUNCTION StripHTML
  (   
      @text VARCHAR(MAX)
  )
  RETURNS TABLE 
  AS
  RETURN
      WITH BracketIndexes (startIndex, stopIndex) AS (SELECT
              CHARINDEX('&lt;', @text),
              CHARINDEX('&gt;', @text) UNION ALL SELECT
              CHARINDEX('&lt;', @text, stopIndex + 1),
              CHARINDEX('&gt;', @text, stopIndex + 1)
          FROM BracketIndexes
          WHERE startIndex &gt; 0 AND stopIndex &gt; 0
      )
      SELECT * FROM BracketIndexes
  </code></pre>
  
  <p>Now that I have the indices of where the <code>&lt;</code>s and <code>&gt;</code> are, I'm not sure how to:</p>
  
  <ul>
  <li>remove the contents between those indices</li>
  <li>return the string so I can cross apply it</li>
  </ul>
  
  <p>I'm thinking I can just concat the left side and right side of each bracket using <code>LEFT</code>/<code>RIGHT</code>, but I can't seem to get the indices correct.</p>
  
  <p>Edit:</p>
  
  <p>This kinda worked:</p>
  
  <pre><code>DECLARE @text VARCHAR(100) = 'HELLO &lt;B&gt;WORLD&lt;/B&gt; asd';
  
  WITH BracketIndexes (startIndex, stopIndex) AS (
      SELECT
          CHARINDEX('&lt;', @text),
          CHARINDEX('&gt;', @text)
      UNION ALL
      SELECT
          CHARINDEX('&lt;', @text, stopIndex + 1) - (stopIndex - startIndex + 1),
          CHARINDEX('&gt;', @text, stopIndex + 1) - (stopIndex - startIndex + 1)
      FROM BracketIndexes
      WHERE startIndex &gt; 0 AND stopIndex &gt; 0
  )
  SELECT @text = LEFT(@text, startIndex - 1) + RIGHT(@text, LEN(@text) - stopIndex)FROM BracketIndexes WHERE startIndex &gt; 0 AND stopIndex &gt; 0
  SELECT @text
  </code></pre>
  
  <p>But when when I added some more HTML statements into <code>@text</code>, the <code>RIGHT</code> had an incorrect length parameter.</p>