Questions on Anime.Se sorted by spoiler %

0

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

DECLARE @qt_st INT;
DECLARE @sp_bdy NVARCHAR(MAX);
DECLARE @qt_bdy NVARCHAR(MAX);
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);
DECLARE @stripped_spoiler 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 );
 
 
-- iterates across every single post in the temporary table
-- second try using a cursor. Thanks to inspiration by @rene
 
DECLARE posts_cursor CURSOR
FOR
SELECT tmp.Id, tmp.Body -- tmp.bdy_amnt, tmp.sp_amnt
FROM #temp_posts AS tmp
FOR UPDATE OF tmp.bdy_amnt, tmp.sp_amnt;
 
OPEN posts_cursor;
 
FETCH FROM posts_cursor
INTO @cur_pst, @qt_bdy;
 
 
WHILE @@FETCH_STATUS = 0
  BEGIN
  
    SET @qt_st = 1;
    SET @to_search = 1;
    SET @sp_bdy=''
    WHILE @qt_st != 0 AND @qt_st IS NOT NULL
      BEGIN
            SET @qt_st = CHARINDEX('<blockquote class="spoiler">', @qt_bdy, @to_search);
            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 @sp_bdy = @sp_bdy + SUBSTRING(@qt_bdy, @qt_st,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  
    
    --if (@sp_bdy is not null)
    --begin
    SET @stripped_spoiler = @sp_bdy;
    SET @sta_string = CHARINDEX('<',@stripped_spoiler)
    SET @end_string = CHARINDEX('>',@stripped_spoiler,CHARINDEX('<',@stripped_spoiler))
    SET @len_string = (@end_string - @sta_string) + 1
    WHILE @sta_string > 0 AND @end_string > 0 AND @len_string > 0
      BEGIN
          SET @stripped_spoiler = STUFF(@stripped_spoiler,@sta_string,@len_string,'');
          SET @sta_string = CHARINDEX('<',@stripped_spoiler);
          SET @end_string = CHARINDEX('>',@stripped_spoiler,CHARINDEX('<',@stripped_spoiler));
          SET @len_string = (@end_string - @sta_string) + 1;
      END  
      UPDATE #temp_posts SET sp_amnt = LEN(LTRIM(RTRIM(@stripped_spoiler))), bdy_amnt = LEN(LTRIM(RTRIM(@stripped_body))) WHERE CURRENT OF posts_cursor;
    --end
    FETCH FROM posts_cursor
    INTO @cur_pst, @qt_bdy;
  END
 
CLOSE posts_cursor;
DEALLOCATE posts_cursor;
 
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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results