0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #mods (UserId int)
INSERT INTO #mods SELECT UserId FROM Badges WHERE Name IN ('Sheriff', 'Constable')
INSERT INTO #mods VALUES (-1)

DECLARE @findstr nvarchar(max) = '"Id":'

SELECT 
  ph.Id, 
  pht.Name,
  ph.PostId AS [Post Link], 
  ph.CreationDate, 
  ph.UserId AS [User Link], 
  ph.Comment, 
  ph.Text 
FROM 
  PostHistory AS ph
  LEFT OUTER JOIN Posts AS p
    ON ph.PostId = p.Id
  LEFT OUTER JOIN PostHistoryTypes AS pht
    ON ph.PostHistoryTypeId = pht.Id
WHERE
  LEN(ph.Text) - LEN(REPLACE(ph.Text, @findstr, '')) = 1*LEN(@findstr)
  -- Exclude protection
  AND ph.PostHistoryTypeId NOT IN (19, 20)
  -- Exclude self-deletion
  AND NOT (ph.PostHistoryTypeId IN (12, 13) AND p.OwnerUserId = ph.UserId)
  -- Exclude moderator actions
  AND UserId NOT IN (SELECT UserId FROM #mods)
  
-- 12 = Post Deleted
-- 13 = Post Undeleted
-- 19 = Question Protected
-- 20 = Question Unprotected

view current version

edited jan 2 15

created jan 2 15 senshin