--- Query to order and identify spoiler-ridden questions on anime.SE

DECLARE @qt_st INT;
DECLARE @qt_cnt INT;
DECLARE @qt_bdy NVARCHAR(MAX);
DECLARE @pst_cnt INT;
DECLARE @pst_cnt_v INT = 1;
DECLARE @cur_pst INT;
DECLARE @to_search INT;

-- first pass stripping HTML tags, adapted from https://stackoverflow.com/a/457725/
DECLARE @sta_string INT;
DECLARE @end_string INT;
DECLARE @len_string INT;
DECLARE @stripped_body NVARCHAR(MAX);


-- at first, all posts containing spoilers are heaped into a temporary table, to improve performance
SELECT
p.Id,
p.Body,
1 AS sp_amnt,
1 AS bdy_amnt,
p.CreationDate

INTO #temp_posts

FROM Posts AS p

WHERE ( CHARINDEX('<blockquote class="spoiler">', p.Body) > 0) AND ( p.PostTypeId = 1 );

SET @pst_cnt = ( SELECT COUNT(*) AS COUNT FROM #temp_posts );

-- iterates across every single post in the temporary table
WHILE @pst_cnt_v <= @pst_cnt
  BEGIN
    SELECT @cur_pst = tmp_pst.Id, @qt_bdy = tmp_pst.Body
    FROM
    (
      SELECT
      #temp_posts.Id AS Id,
      #temp_posts.Body AS Body,
      ROW_NUMBER() OVER (ORDER BY #temp_posts.Id ASC) AS cur_row
      FROM #temp_posts
    ) AS tmp_pst
    WHERE cur_row = @pst_cnt_v;

    SET @qt_st = 1;
    SET @qt_cnt = 0;
    SET @to_search = 1;
    WHILE @qt_st != 0 AND @qt_st IS NOT NULL
      BEGIN
            -- CHARINDEX returns the starting position of the
            -- search string and the search string is 27 chars long
            SET @qt_st = CHARINDEX('<blockquote class="spoiler">', @qt_bdy, @to_search) + 27;
            SET @to_search = @to_search + @qt_st;
        IF @to_search >= LEN(@qt_bdy)
            SET @qt_st = 0;
        IF @qt_st != 0 AND @qt_st IS NOT NULL
          SET @qt_cnt = @qt_cnt + ( CHARINDEX('</blockquote>', @qt_bdy, @qt_st) - @qt_st );
      END
    -- first pass attempting to strip HTML tags before processing, adapted from https://stackoverflow.com/a/457725/
    SET @stripped_body = @qt_bdy;
    SET @sta_string = CHARINDEX('<',@stripped_body)
    SET @end_string = CHARINDEX('>',@stripped_body,CHARINDEX('<',@stripped_body))
    SET @len_string = (@end_string - @sta_string) + 1
    WHILE @sta_string > 0 AND @end_string > 0 AND @len_string > 0
      BEGIN
          SET @stripped_body = STUFF(@stripped_body,@sta_string,@len_string,'')
          SET @sta_string = CHARINDEX('<',@stripped_body)
          SET @end_string = CHARINDEX('>',@stripped_body,CHARINDEX('<',@stripped_body))
          SET @len_string = (@end_string - @sta_string) + 1
      END
    UPDATE #temp_posts SET sp_amnt = @qt_cnt, bdy_amnt = LEN(LTRIM(RTRIM(@stripped_body))) WHERE Id = @cur_pst
    SET @pst_cnt_v = @pst_cnt_v + 1;
  END

SELECT

Id AS [Post Link],
CreationDate AS [Post DATE],
bdy_amnt AS [Lenght OF Post (chars)],
sp_amnt AS [Spoiler Content (chars)],
( sp_amnt * 100 / bdy_amnt ) AS [Percent Spoiler]

FROM #temp_posts

ORDER BY ( sp_amnt * 100 / bdy_amnt ) DESC