Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 TOP 10
- 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;
- 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
- 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;
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement