Unprocessed tag creation requests

0

Please login or register to vote for this query.

(click on this box to dismiss)

Spanish Language

Q&A for linguists, teachers, students and Spanish language enthusiasts in general wanting to discuss the finer points of the language

create procedure #extract_tag
  @post_title nvarchar(250),
  @extracted nvarchar(35) out
as
begin
  if charindex(']', @post_title) - charindex('[', @post_title)-1 > 0
    set @extracted = substring(
      @post_title, 
      charindex('[', @post_title) + 1,
      charindex(']', @post_title) - charindex('[', @post_title)-1
    )
end
go

declare @results table (
  id int,
  tags nvarchar(250),
  title nvarchar(250),
  extracted nvarchar(35) collate SQL_Latin1_General_CP1_CS_AS,
  chat nvarchar(max)
)

declare @unlike table (
  n nvarchar(35) collate SQL_Latin1_General_CP1_CS_AS not null
)

insert into @unlike
values
('status-completed'),
('status-declined'),
('tag-synonyms'),
('tag-wiki'),
('tag-excerpt'),
('bug'),
('stack-overflow-for-teams'),
('burninate-request'),
('blocklist-request'),
('synonym-request'),
('clean-up'),
('search'),
('documentation'),
('spam'),
('scope'),
('syntax-highlighting'),
('suggested-edits'),
('questions'),
('burninate-system'),
('stack-overflow-jobs'),
('answers'),
('developer-story'),
('tag-disambiguation'),
('canonical'),
('magic-links'),
('tag-tips'),
('design'),
('duplicate-questions'),
('review-audits'),
('migration')

declare @id int
declare @tags nvarchar(250)
declare @title nvarchar(250)
declare @extracted nvarchar(35)

declare c cursor FORWARD_ONLY for
  select
  id, tags, title
  from
  posts
  where
  (1=1)
  and (
    (1=0)
    or lower(title) like '%creat%'
    or lower(title) like '%add%'
  )
  and lower(title) like '%tag%'
  and (select top(1) n from @unlike where tags like '%<'+n+'>%') is null
open c;
fetch NEXT from c into @id, @tags, @title;
while @@FETCH_STATUS = 0 
begin
    execute #extract_tag @title, @extracted out;
    
    if @extracted in (
      select tagname from [StackOverflow]..tags where @extracted = tagname
    )
    insert into @results values (
      @id, @tags, @title, @extracted,
      '[tag:'+@extracted+'] complete (['+@title+'](https://meta.stackoverflow.com/q/'+cast(@id as nvarchar)+'))'
    );
    
    FETCH NEXT FROM c INTO @id, @tags, @title;
END;
CLOSE c;
DEALLOCATE c;

select 
   id as [Post Link],
   tags,
   extracted,
   chat
from 
  @results
order by
  tags desc

Enter Parameters

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