Find short "useless" comments

0

Please login or register to vote for this query.

(click on this box to dismiss)

DO NOT auto-flag comments using this script. Insure the comments don't have any useful information

Anime and Manga

Q&A for anime and manga fans

WITH Marks as (
  SELECT '%accept%answer%' as Value
  UNION
  SELECT '%mark %answer%'
  UNION
  SELECT '%marked %answer%'
  UNION
  SELECT '%accept%rate%'
  UNION
  SELECT '%mark %accept%'
  UNION
  SELECT '%marked %accept%'
  UNION
  SELECT '%accept%mark %'
  UNION
  SELECT '%accept%marked %'
  UNION
  SELECT '%lease%accept%'
  UNION
  SELECT '%answer%list%question%'
  UNION
  SELECT '%unanswered%question%list%'
  UNION
  SELECT '%question%unanswered%list%'
  UNION
  SELECT '%check%mark%vote%'
  UNION
  SELECT '%48 hour%'
  UNION
  SELECT '% 2 day%'
),
Instants as (
  Select '%thank you%' as Value
  UNION
  SELECT '%i %see %thanks%'        -- ok i see thanks
  UNION
  SELECT '%as am i%'             -- as am i
  UNION
  SELECT '%thanks%did%trick%'      -- Thanks That did the trick
  UNION
  SELECT '%thanks %correct%'       -- thanks for the correction
  UNION
  SELECT '%thanks%help%'           -- Thanks for the help
  UNION
  SELECT '%thanks %a %lot%'        -- Thanks a lot : )
  UNION
  SELECT '%thanks %for%edit%answ%' -- Thanks for editing our answer!
  UNION
  SELECT '%wow%thanks%'            -- Wow thanks!
  UNION
  SELECT '%help%thanks%'           -- Alright, that is super helpful. Thanks
  UNION
  SELECT '%very%useful%thanks%'    -- Very useful, thanks.
  UNION
  SELECT '%that%was%it%thanks%'    -- Yes that was it. Thanks!
  UNION
  SELECT '%looks%great%thanks%'    -- Looks great, thanks a lot!
  UNION
  SELECT '%good %idea'             -- good idea
  UNION
  SELECT '%great %idea'             -- good idea
  UNION
  SELECT '%that %work%thanks%'     -- that works thanks
  UNION
  SELECT '%will %do %thanks%'      -- will do thanks
  UNION
  SELECT '%i %have %edit% it%'     -- I have edited it...
  UNION
  SELECT '%alrightr%'              -- alright!
  UNION
  SELECT '%thumper%'               -- thumper!
  UNION
  SELECT 'bravo%'                  -- bravo
  UNION
  SELECT 'clever%'                 -- clever
  UNION
  SELECT 'hahaha%'                 -- hahaha
  UNION
  SELECT '[+-]1 %'
  UNION
  SELECT '%you%welcome%glad%help%' -- You're welcome! Glad to help
  UNION
  SELECT '%your welcome%'
  UNION
  SELECT '%questions/1732348%/%1732454#1732454%' -- Zalgo links
  UNION
  SELECT '%what%have%you%tried%'
  UNION
  SELECT '%whathaveyoutried.com%'
  UNION
  SELECT '%fakedemic%'
  UNION
  SELECT '%covid%'
  UNION
  SELECT '%nice%answer%'
  UNION
  SELECT '%ah %i %see%'     --  Ah, I see......
  UNION
  SELECT '%okay%'     --  Okay......
  UNION
  SELECT '%bump%'     --  Bump.....
  UNION
  SELECT '%everything%'     --  Everything.....
  UNION
  SELECT '%all%right%'     --  All right.....
  UNION
  SELECT '%...%'
  UNION
  SELECT '%...'
  UNION
  SELECT '%see%below%'
  UNION
  SELECT '%yes%you%can%'
  UNION
  SELECT '%very%nice%'
),
Likes as (
  Select '%thanks%' as Value
  UNION
  SELECT 'thxs%'
  UNION
  SELECT '%thx %'
  UNION
  SELECT '%your%welcome%'
  UNION
  SELECT '%you_r%welcome%'
  UNION
  SELECT '%glad%help%'
  UNION
  SELECT '%no prob%'
  UNION
  SELECT '%no worr%'
  UNION
  SELECT '%it works%'
  UNION
  SELECT 'flag[^a-z]%'
  UNION
  SELECT '[^a-z]flag %'
  UNION
  SELECT '%vote %to %close%'
  UNION
  SELECT '% voted%'
  UNION
  SELECT '%voter%'
  UNION
  SELECT '%downvote%'
  UNION
  SELECT 'voted %'
  UNION
  SELECT '%vote%helpful%'
  UNION
  SELECT '%sounds%like%homework%'
  UNION
  SELECT '%have%you%tried%'
  UNION
  SELECT '%you%have%tried%'
  UNION
  SELECT '%have%you%writt%'
  UNION
  SELECT '%answer%correct%'
  UNION
  SELECT '%add%answer%'
  UNION
  SELECT '%best%answer%'
  UNION
  SELECT '%post%answer%'
  UNION
  SELECT '%well%spotted%'
  UNION
  SELECT 'done[^a-z]%'
  UNION
  SELECT '%[^a-z]done%'
  UNION
  SELECT '%lmgtfy%'
  UNION
  SELECT '%google%'
  UNION
  SELECT '%updated%'
  UNION
  SELECT '[("][+-]1%'
  UNION
  SELECT '[+-]1 %'
  UNION
  SELECT 'welcome%'
  UNION
  SELECT '%idk %'
  UNION
  SELECT 'idk%'
  UNION
  SELECT '%huh[ \?]%'
  UNION
  SELECT 'no %no %no %'
  UNION
  SELECT 'blah %'
  UNION
  SELECT '%my %pleasu%'
  UNION
  SELECT '%aim %please%'
  UNION
  SELECT '%good %luck%'
  UNION
  SELECT '%edited%'
  UNION
  SELECT '%any %time%'
  UNION
  SELECT '%happy %help%'
  UNION
  SELECT '% sure%'
  UNION
  SELECT '%include%sugg%'
  UNION
  SELECT '%sh[i]t%'
  UNION
  SELECT '%p[i]ss%'
  UNION
  SELECT '%c[u]nt%'
  UNION
  SELECT '%w[h]ore%'
  UNION
  SELECT '%b[i]tch%'
  UNION
  SELECT '%c[u]nt %'
  UNION
  SELECT '%f[u]ck %'
  UNION
  SELECT '%d[i]ck %'
  UNION
  SELECT '%d[i]ckh[e]ad%'
  UNION
  SELECT '%as[s]hole%'
  UNION
  SELECT '%b[a]stard%'
  UNION
  SELECT '%d[a]mn%'
  UNION
  SELECT '%c[u]nt%'
  UNION
  SELECT '% w[t]f%'
  UNION
  SELECT '%w[t]f %'
  UNION
  SELECT '%[^a-z]r[e]tard%'
  UNION
  SELECT '%rtfm%'
  UNION
  SELECT '%stupid%'
  UNION
  SELECT '%i[d]iot%'
  UNION
  SELECT '%your brain%'
  UNION
  SELECT '%jerk%'
  UNION
  SELECT '%i[d]jit%'
  UNION
  SELECT '%silly%'
),
MyComment (Id, PostId, Text, Marks, Normal, Fast, CreationDate)  
AS  
(  
SELECT 
  c.Id,
  c.PostId,
  c.Text,
  (
    SELECT count(*) FROM Marks
    WHERE lower(c.Text) Like Marks.Value
  )*30 as Marks,
  (
    SELECT count(*) FROM Likes
    WHERE lower(c.Text) Like Likes.Value
  ) as Normal,
  (
    SELECT count(*) FROM Instants
    WHERE lower(c.Text) Like Instants.Value
  )*10 AS Fast,
  c.CreationDate
  FROM Comments c
  WHERE c.Id between ##FirstID:int## and ##LastID:int##
    AND len(c.Text) BETWEEN ##FirstLength:int## AND ##LastLength:int##
),
Final (Id, PostId, Text, Found, CreationDate)  
AS  
(  
SELECT 
  c.Id,
  c.PostId,
  c.Text,
  (
    sum(c.Marks)
    +
    sum(c.Normal)
    +
    sum(c.Fast)
    +
    -- Emoji and other Unicode characters  (😂😙😭🚀🤣):
    -- Count them as 3 points each
    (
      len(c.Text)
      -
      len(replace(replace(CONVERT(varchar(MAX),replace(Text,'?','SAVEQUESTION')), '?', ''), 'SAVEQUESTION', '?'))
    ) * 3
  ) as Found,
  c.CreationDate
  FROM MyComment c
  GROUP BY Id, CreationDate, Text, PostId
)
SELECT TOP 5000 CreationDate,Found, PostId, Id as [Comment Link], text
FROM Final
WHERE (##LastLength:int## <= 15 AND len(Final.Text) <= 15)
   OR (##FirstLength:int## = ##LastLength:int## AND len(Final.Text) = ##LastLength:int##)
   OR (##LastLength:int## > 15 AND Found>=##Minimum:int##)
GROUP BY Found, CreationDate, Id, Text, PostId
ORDER BY Found DESC, CreationDate ASC

Enter Parameters

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