3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for speakers of German wanting to discuss the finer points of the language and translation

declare @title nvarchar(4000), @postId int, @nc int;
declare @openwords table (word nvarchar(4000));
declare @ncwords table (word nvarchar(4000));
declare @word nvarchar(4000), @to int, @i int  = 0, @sampled int = 0;

declare crs cursor read_only forward_only for
select top(10000) lower(p.Title), p.Id, 
   case when h.PostId is null then 1 else 0 end as nc
from Posts p
left join (
  select PostId 
  from PostHistory 
  where Comment = '3' and PostHistoryTypeId = 10) h
on p.Id = h.PostId
where p.PostTypeId = 1
order by PostId;

open crs;

fetch next from crs into @title, @postId, @nc;
while 0 = @@fetch_status
begin
  if @nc=1 or rand() > 0.95 
  begin
    raiserror (N'At title: %s', 0,1, @title);
    while 1=1
    begin
      -- discard everything before first a-z
      set @to = patindex(N'[a-z]%', @title);
      
      if (0 = @to)
      begin
         raiserror (N'No more words in %s', 0,3, @title);
         break;
      end
      
      set @title = substring(@title, @to, 8000);
      
      -- find next non a-z
      set @to = patindex(N'%[^a-z]%', @title);
      if @to = 0
      begin
         -- this is the last word in the title
        set @to = len(@title);
      end
      
      set @word = substring(@title, 1, @to-1);
      set @title = substring(@title, @to+1, 8000);
      raiserror (N'Found word: ''%s'' Left: ''%s''', 0,1, @word, @title);
    end
    set @sampled += 1;
  end
  fetch next from crs into @title, @postId, @nc;
  set @i += 1;
  if 0 =  @i % 1000
  begin
  raiserror (N'Processed %d titles (sampled %d). At postId: %d', 0, 2, 
    @i, @sampled, @postId);
  end
end

close crs;
deallocate crs;

view current version

edited apr 27 12

created apr 27 12 remusrusanu