0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- http://meta.stackexchange.com/questions/234911/can-we-have-functions-in-sede/234983#234983

-- Strip off code blocks
create procedure #remove_code_blocks(
  @original nvarchar(max),
  @s nvarchar(max) output
) as begin
  declare @i bigint
  set @s = @original
  set @i = patindex('%<code>%', @s)
  while @i <> 0
  begin
    set @s = left(@s, @i-1) + right(@s, len(@s) - patindex('%</code>%', @s) - 6)
    set @i = patindex('%<code>%', @s)
  end
end
go

-- Post bodies without code blocks
create table #temp (Id int, Body nvarchar(max))

declare cur cursor for
  select Id, Body
  from Posts
  where Body like '%[^->]---[^-<]%'
declare @id int
declare @original_body nvarchar(max)
declare @transformed_body nvarchar(max)
open cur
fetch next from cur into @id, @original_body
while @@fetch_status = 0
begin
  execute #remove_code_blocks @original_body, @transformed_body out
  insert into #temp values (@id, @transformed_body)
  fetch next from cur into @id, @original_body
end
close cur

select top 100
  Id as [Post Link],
  Body
from #temp
where Body like '%[^-<> ]---[^-<>]%' or Body like '%[^-<>]---[^-<> ]%'
order by Id asc

fork query

edited jul 19 14

created jul 19 14 Gilles