3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

declare @word nvarchar(max) = '%##word##%'
declare @post_body nvarchar(max)
declare @post_id int
declare @wordPos int
declare @httpPos int
declare @url nvarchar(250)
declare @fullUrl nvarchar(250)

declare posts_crs CURSOR FOR  
  SELECT TOP(##num?500##)
  p.Id, p.Body
  FROM Posts p INNER JOIN Posts q ON q.Id=COALESCE(p.ParentId,p.Id)
  WHERE p.id in (302023, 104770)
  and (p.Body Like @word)
  ORDER BY q.LastActivityDate DESC

OPEN posts_crs;  
  
FETCH NEXT FROM posts_crs
  INTO @post_id, @post_body;
  
WHILE @@FETCH_STATUS = 0  
BEGIN  

    set @wordPos = patindex(@word, @post_body)
    -- print @wordPos
    set @url = substring(@post_body, @wordPos - 30, 30 + len(@word))
    -- print @url
    set @httpPos = patindex('%http%', @url)
    -- print @httpPos
    set @fullUrl = substring(@url, @httpPos, len(@url) - @httpPos)
    
    print concat('id: ', @post_id , ', url: ' , @fullUrl)
    
    FETCH NEXT FROM posts_crs
      INTO @post_id, @post_body;
END;  
  
CLOSE posts_crs;  
DEALLOCATE posts_crs;

fork query

edited apr 28 22

created apr 28 22 rene