Find targets for duplicate posts


Please login or register to vote for this query.

(click on this box to dismiss)

Each duplicate question has up to three other questions as the non-duplicate posts. This query finds the posts that duplicate questions point to. It mostly works, but no guarantees.


Q&A for community managers, administrators, and moderators

create table #DupeStrings (Dupe INT, Targets VARCHAR(255),
    Tgt1 VARCHAR(255), Tgt2 VARCHAR(255), Tgt3 VARCHAR(255))
insert into #DupeStrings
select top 200 PostId as [Post Link],
    -- Grab text from after the first '[' to the first ']'
        charindex('[', Text) + 1,
        charindex(']', Text) - charindex('[', Text) - 1
    ) + ',,,',
    0, 0, 0
from PostHistory h
where PostHistoryTypeId = 10
and Comment in (1, 101)
order by h.CreationDate desc

update #DupeStrings
set tgt1 = left(Targets, charindex(',', Targets) - 1),
    tgt2 = substring(Targets, charindex(',', Targets) + 1, 100),
    tgt3 = substring(Targets, charindex(',', Targets) + 1, 100)

update #DupeStrings
set tgt2 = left(tgt2, charindex(',', tgt2) - 1),
    tgt3 = substring(tgt3, charindex(',', tgt3) + 1, 100)

update #DupeStrings
set tgt3 = left(tgt3, charindex(',', tgt3) - 1)

select * from #DupeStrings

select Dupe,
       -- Tgt1: everything up to the first comma
       left(Targets, charindex(',', Targets) - 1) as Tgt1,
       -- Tgt2:
           -- just after the first comma:
           charindex(',', Targets) + 1,
           -- until the second comma:
               -- replace the first comma with 'a' so we can find the second comma
               stuff(Targets, charindex(',', Targets), 1, 'a')
           -- and subtract the length up to the first comma:
           ) - charindex(',', Targets) - 1
       ) as Tgt2
       -- Tgt3:
into #Dupes
from #DupeStrings

    Dupe as [Post Link],
    cast(Tgt1 as INT) as [Post Link],
    cast(Tgt2 as INT) as [Post Link]
from #Dupes
left join Posts d on Dupe = d.Id
left join Posts t1 on Tgt1 = t1.Id
left join Posts t2 on Tgt2 = t2.Id
where (t1.Tags like '%story-identification%'
    or t2.Tags like '%story-identification%'
    or Targets like '%[0-9]%,[0-9]%,[0-9]%')
    and d.Tags not like '%story-identification%'

--select Dupe as [Post Link], FirstTarget as [Post Link], TgtPost1.Tags
--from #Dupes
--left join Posts DupePost on DupePost.Id = Dupe
--left join Posts TgtPost1 on TgtPost1.Id = FirstTarget
--where DupePost.Tags not like '%<story-identification>%'
      --and TgtPost1.Tags like '%<story-identification>%'

Enter Parameters

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